Laravel: join Eloquent models

Question

Question. How can I use Eloquent to produce this query:

SELECT campaigns.name, users.name FROM campaigns LEFT JOIN users on campaigns.gamemaster_id = users.id where campaigns.status = 1

Campaigns

id  gamemaster_id name        status
1   1             campaign1   1
2   2             campaign2   1

Users

id name
1  rick
2  bob

Result

id gamemaster_id name      status gamemaster_name
1  1             campaign1 1      rick
2  2             campaign2 1      bob

Campaign model

class Campaign extends Model
{
    public function gamemaster()
    {
        return $this->belongsTo('App\User', 'gamemaster_id');
    }
}

My try to make Eloquent, but that fails:

$result = Campaign::where('status', '=', 1)->with('gamemaster')->select('name')->orderBy('users.updated_at', 'desc')->get();

Burak Burak · Accepted Answer · 2016-06-14T16:18:59

You can do it in two ways, first using the query builder,

$campaigns = DB::table('campaigns')->leftJoin('users', 'campaigns.gamemaster_id', '=', 'users.id')
            ->select(['campaigns.name as name', 'users.name as gamemaster_name'])
            ->where('campaigns.status', '=', 1)
            ->orderBy('users.updated_at', 'desc')
            ->get();

And with eager loading, you can get the campaigns like below. However, to sort by relation property, you need an another layer which is not that easy to implement.

$campaigns = Campaign::where('status', '=', 1)->with(['gamemaster' => function($query){
    $query->select(['id', 'name']);
}]->select('id', 'gamemaster_id', 'name')->get();

But you can use collection functions to easily sort it (However, it will take more execution time)

$campaigns = Campaign::where('status', '=', 1)->with(['gamemaster' => function($query){
    $query->select(['id', 'gamemaster_id', 'name', 'updated_at']);
}]->select('id', 'name')->get()->sortByDesc(function ($campaign) {
    return $campaign->gamemaster->updated_at;
})

Laravel: join Eloquent models

2 Answers