Assign Raw query to Model

I want to make a custom query with DB class then i want to get all the benefits of the model, it's possible?

public function fc()
    {
        $latestQueries = DB::table('queries')
                                ->select('user_id', DB::raw('MAX(id) as last_query_id'))
                                ->groupBy('user_id');

        $users= DB::table('users')
            ->joinSub($latestQueries, 'latest_queries', function ($join) {
                $join->on('users.id', '=', 'latest_queries.user_id');
            })
            ->leftJoin('queries', 'last_query_id', '=', 'queries.id')
            ->leftJoin('locations', 'locations.id', '=', 'queries.location_id')
            ->leftJoin('models', 'models.id', '=', 'users.model_id')
            ->get();
        
        return view('/users.index', compact('users'));
    }

Then in the index i want use my User.php Class custom function like

@foreach($users as $user)
    $user->path()
@endforeach

class User extends Model
{
    protected $guarded = [];

    public function path()
    {
        return "/users/{$this->id}";
    }
}

It's possible? How?

Braunson

6 years ago

Level 18

Why not just use Eloquent ORM with the Models in the first place? As for your question: Take a look at the hydrate and hydrateRaw methods on your eloquent model. Should do what you want :)

1 like

jlrdw

6 years ago

Best Answer

Level 75

I want to make a custom query with DB class then i want to get all the benefits of the model

Then instead of query builder use an eloquent query. An example "eloquent" query:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Again just example. You still have to set up your models, example:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Powner extends Model
{

    protected $table = 'dc_powners';
    protected $primaryKey = 'ownerid';
    protected $connection = 'mysqlv2';  // you won't need
    protected $fillable = [
        'ownerid',
        'oname',
        'ostreet',
        'odate',
        'ocheck'
    ];
    public $timestamps = [];

}

Just test data I use and just examples.

After model setup, query away.

1 like

Please or to participate in this conversation.