laravel PHP 在 eloquent 模型中运行原始 SQL 查询

PHP run a raw SQL query in eloquent model

提问by twigg

I'm trying to use Eloquent ORM from Laravel in my own legacy project. I have the models set-up and Eloquent is working fine but I can't understand how to run a raw SQL query using the eloquent database connection inside the model. I need to run raw SQL queries for now until I can refactor the entire code base over to use the ORM.

我正在尝试在我自己的遗留项目中使用 Laravel 的 Eloquent ORM。我设置了模型并且 Eloquent 工作正常，但我无法理解如何使用模型内的 eloquent 数据库连接运行原始 SQL 查询。我现在需要运行原始 SQL 查询，直到我可以重构整个代码库以使用 ORM。

namespace App\Providers;

use Illuminate\Database\Capsule\Manager;
use League\Container\ServiceProvider\AbstractServiceProvider;

class DatabaseServiceProvider extends AbstractServiceProvider
{

    protected $provides = [
        Manager::class
    ];

    public function register()
    {

        $container = $this->getContainer();

        $config = $container->get('config');

        $config = $config->get('db.mysql');

        $capsule = new Manager;

        $capsule->addConnection([
            $config
        ]);

        $capsule->setAsGlobal();
        $capsule->bootEloquent();

        $container->share(Manager::class, function () use ($capsule){
            return $capsule;
        });

    }

}

Now in my model I have the following:

现在在我的模型中，我有以下内容：

namespace App\Models;

use Illuminate\Database\Eloquent\Model as Eloquent;

class User extends Eloquent
{
    protected $table = '_users';
}

Then I'm trying to call that model in my controller:

然后我试图在我的控制器中调用该模型：

namespace App\Controllers;

use App\Views\View;
use App\Models\User;
use Psr\Http\Message\RequestInterface;
use Psr\Http\Message\ResponseInterface;

class HomeController
{

    protected $view;

    public function __construct(View $view)
    {
        $this->view = $view;
    }

    public function index(RequestInterface $request, ResponseInterface $response)
    {
        $user = User::find(1);

        dump($user);

    }
}

So this all works nicely but I can't figure out how to run a raw query inside of the model? Obviously the below won't work but I want something like that, creating a new function and it returns the result:

所以这一切都很好，但我不知道如何在模型内部运行原始查询？显然下面的不起作用，但我想要类似的东西，创建一个新函数并返回结果：

Model:

模型：

public function customQuery()
{
    $query = 'SELECT * FROM _users';
    return $query;
}

Controller:

控制器：

$user = new User;
$user->customQuery();

回答by Amitesh

Laravel makes interacting with databases extremely simple across a variety of database backends using either raw SQL, the fluent query builder, and the Eloquent ORM.

Laravel使得与数据库横跨多种使用后端数据库的极其简单的交互无论是原始的SQL，流畅的查询生成器，和雄辩ORM。

All three has there own standard syntax format.

这三个都有自己的标准语法格式。

Recommendation: You should not create raw SQL inside model instead you can create even in controller (Though its recommended to follow repository pattern) but as a beginner following step will give you your expected result

建议：您不应该在模型内部创建原始 SQL，您甚至可以在控制器中创建（尽管建议遵循存储库模式）但作为初学者，以下步骤将为您提供预期的结果

Update your HomeController for following

更新您的 HomeController 以进行以下操作

1. Use the Illuminate\Support\Facades\DB;

2. Update the index method

   public function index()
   {
       $users = DB::select('SELECT * FROM users');
   
       dd($users);
   }
   

1. 使用Illuminate\Support\Facades\DB;

2. 更新索引方法

   public function index()
   {
       $users = DB::select('SELECT * FROM users');
   
       dd($users);
   }
   

Reference : https://laravel.com/docs/5.6/database#running-queries

参考：https: //laravel.com/docs/5.6/database#running-queries

Laravel is great framework. Keep learning !!

Laravel 是一个很棒的框架。保持学习 ！！

回答by Vipul

DB::raw()is used to make arbitrary SQL commands which aren't parsed any further by the query builder. Check this ref. link, with more details: http://fideloper.com/laravel-raw-queries

DB::raw()用于生成查询构建器不再进一步解析的任意 SQL 命令。检查这个参考。链接，更多细节：http: //fideloper.com/laravel-raw-queries

Exampleof \DB::rawand \DB::select

实施例的\DB::raw并\DB::select

回答by Adnan Mumtaz

use Laravel scopes()this way you can easily use eloquent syntax.

以scopes()这种方式使用 Laravel，您可以轻松使用雄辩的语法。

$user = new User::customQuery();

public function scopeSustomQuery($query)
{
    $query = $query->where('You condition');
    return $query;
}

回答by mehlichmeyer

try the whereRaw() function

试试 whereRaw() 函数

https://laravel.com/docs/5.6/queries

https://laravel.com/docs/5.6/queries

From Documentation:

从文档：

The whereRaw and orWhereRaw methods can be used to inject a raw where clause into your query. These methods accept an optional array of bindings as their second argument:

$orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get();

whereRaw 和 orWhereRaw 方法可用于将原始 where 子句注入您的查询。这些方法接受一个可选的绑定数组作为它们的第二个参数：

$orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get();

回答by Piterden

Try this and never use any raw queries in Laravel

试试这个，不要在 Laravel 中使用任何原始查询

/* @var User $query */
$user = new User();

/* @var Builder $query */
$query = $user->newQuery();

dd($query->select('*')->from('_users')->get());