How can I query raw via Eloquent?

Question

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.

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

Answer 1

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

Answer 2

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-Expressions on Laravel website.

Answer 3

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

Below code is equviolent to $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\Builder as:

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

Answer 4

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

/**
 * 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);
     }
 }

Answer 5

Old question, already answered, I know.

However, nobody seems to mention the Expression class.

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.

Include the following class in your Model file:

use Illuminate\Database\Query\Expression;

Then inside the Model class define a new variable

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)

Answer 6

use Eloquent Model related to the query you're working on.

and do something like this:

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

Answer 7

You could shorten your result handling by writing

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

Answer 8

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

Answer 9

if ever you might also need this. orderByRaw() function for your order by.

Like

WodSection::orderBy('score_type')

->orderByRaw('FIELD(score_type,"score_type") DESC')

->get();