php 如何通过 Eloquent 查询原始数据？

How can I query raw via Eloquent?

提问by Chilion

I am trying to do a query in my Laravel app and I want to use a normal structure for my query. This class either does use Eloquent so I need to find something to do a query totally raw.

我正在尝试在我的 Laravel 应用程序中进行查询，并且我想对我的查询使用正常结构。这个类要么使用 Eloquent，所以我需要找到一些东西来做一个完全原始的查询。

Might be something like Model::query($query);. Only that doesn't work.

可能是这样的Model::query($query);。只是那样是行不通的。

采纳答案by Ray

use DB::statement('your raw query here'). Hope this helps.

使用DB::statement('your raw query here'). 希望这可以帮助。

回答by The Alpha

You may try this:

你可以试试这个：

// query can't be select * from table where
Model::select(DB::raw('query'))->get();

An Example:

一个例子：

Model::select(DB::raw('query'))
     ->whereNull('deleted_at')
     ->orderBy('id')
     ->get();

Also, you may use something like this (Using Query Builder):

此外，您可以使用这样的东西（使用查询生成器）：

$users = DB::table('users')
                 ->select(DB::raw('count(*) as user_count, status'))
                 ->where('status', '<>', 1)
                 ->groupBy('status')
                 ->get();

Also, you may try something like this (Using Query Builder):

此外，您可以尝试这样的操作（使用查询生成器）：

$users = DB::select('select * from users where id = ?', array(1));
$users = DB::select( DB::raw("select * from users where username = :username"), array('username' => Input::get("username")));

Check more about Raw-Expressionson Laravel website.

Raw-Expressions在Laravel 网站上查看更多信息。

回答by Orkhan Alikhanov

You can use hydrate()function to convert your array to the Eloquent models, which Laravelitself internallyuses to convert the query results to the models. It's not mentioned in the docs as far as I know.

您可以使用hydrate()function 将您的数组转换为 Eloquent 模型，它Laravel本身在内部用于将查询结果转换为模型。据我所知，文档中没有提到它。

Below code is equviolent to $userModels = User::where('id', '>', $userId)->get();:

下面的代码相当于$userModels = User::where('id', '>', $userId)->get();：

$userData = DB::select('SELECT * FROM users WHERE id > ?', [$userId]);
$userModels = User::hydrate($userData);

hydrate()function is defined in \Illuminate\Database\Eloquent\Builderas:

hydrate()函数定义\Illuminate\Database\Eloquent\Builder为：

/**
 * Create a collection of models from plain arrays.
 *
 * @param  array  $items
 * @return \Illuminate\Database\Eloquent\Collection
 */
public function hydrate(array $items) {}

回答by Matthijn

I don't think you can by default. I've extended Eloquent and added the following method.

我不认为你可以默认。我扩展了 Eloquent 并添加了以下方法。

/**
 * Creates models from the raw results (it does not check the fillable attributes and so on)
 * @param array $rawResult
 * @return Collection
 */
public static function modelsFromRawResults($rawResult = [])
{
    $objects = [];

    foreach($rawResult as $result)
    {
        $object = new static();

        $object->setRawAttributes((array)$result, true);

        $objects[] = $object;
    }

    return new Collection($objects);
}

You can then do something like this:

然后你可以做这样的事情：

class User extends Elegant { // Elegant is my extension of Eloquent

     public static function getWithSuperFancyQuery()
     {
         $result = DB::raw('super fancy query here, make sure you have the correct columns');
         return static::modelsFromRawResults($result);
     }
 }

回答by cartbeforehorse

Old question, already answered, I know.

老问题，已经回答了，我知道。

However, nobody seems to mention the Expression class.

然而，似乎没有人提到 Expression 类。

Granted, this might not fix your problem because your question leaves it ambiguous as to where in the SQL the Raw condition needs to be included (is it in the SELECT statement or in the WHERE statement?). However, this piece of information you might find useful regardless.

当然，这可能无法解决您的问题，因为您的问题对于需要在 SQL 中的哪个位置包含原始条件（是在 SELECT 语句中还是在 WHERE 语句中？）留下了歧义。但是，无论如何，您可能会发现这条信息很有用。

Include the following class in your Model file:

在您的模型文件中包含以下类：

use Illuminate\Database\Query\Expression;

Then inside the Model class define a new variable

然后在 Model 类中定义一个新变量

protected $select_cols = [
    'id', 'name', 'foo', 'bar',
    Expression ('(select count(1) from sub_table where sub_table.x = top_table.x) as my_raw_col'), 'blah'
]

And add a scope:

并添加一个范围：

public function scopeMyFind ($builder, $id) {
    return parent::find ($id, $this->select_cols);
}

Then from your controller or logic-file, you simply call:

然后从您的控制器或逻辑文件中，您只需调用：

$rec = MyModel::myFind(1);
dd ($rec->id, $rec->blah, $rec->my_raw_col);

Happy days.

快乐的时光。

(Works in Laravel framework 5.5)

（适用于 Laravel 框架 5.5）

回答by AbdessamadEL

use Eloquent Modelrelated to the queryyou're working on.

使用与您正在处理的查询相关的Eloquent 模型。

and do something like this:

并做这样的事情：

$contactus = ContactUS::select('*')
    ->whereRaw('id IN (SELECT min(id) FROM users GROUP BY email)')
    ->orderByDesc('created_at')
    ->get();

回答by Christo Naudé

if you want to select info it is DB::select(Statement goes here)just remember that some queries wont work unless you go to Config/Database.php and set connections = mysql make sure 'strict' = false Just know that it can cause some security concerns

如果您想选择信息， DB::select(Statement goes here)请记住，除非您转到 Config/Database.php 并设置 connections = mysql 确保 'strict' = false ，否则某些查询将无法工作，只需知道它可能会导致一些安全问题

回答by Mathias Lieber

You could shorten your result handling by writing

你可以通过写来缩短你的结果处理

$objects = new Collection(array_map(function($entry) {
    return (new static())->setRawAttributes((array) $entry, true);
}, $result));