Ad

Eloquent 2 Left Join Query

- 1 answer

I would like to execute the following query using Laravel's eloquent query builder:

SELECT a.*, b.day, c.sex FROM hours as a
left join reserves as b
on a.id = b.hour_id and b.day = '2015-12-12'
left join doctors as c
on a.doctor_id = c.id
where a.doctor_id = 1;

I have tried the following:

Hour::leftJoin('reserves', function($join) {
    $join->on('hours.id' , '=' , 'reserves.hour_id')
        ->where('reserves.day' , '=' , '2015-12-12');
})
->leftJoin('doctors', function($join) {
    $join->on('hours.doctor_id', '=', 'doctors.id'); 
})
->where('hours.doctor_id', '=', $doctorId)
->get();

Unfortunately, I am not getting the same results.

Ad

Answer

Everything looks fine, however you miss selecting columns, after Hour:: you should add:

select('hours.*'.'reserves.day','doctors.sex')->

You could also make second join simpler and use aliases, so the final code could look like this:

Hour::select('hours.*'.'reserves.day','doctors.sex')
     ->from('hours AS a')
     ->leftJoin('reserves AS b', function($join) {
         $join->on('a.id' , '=' , 'b.hour_id')
        ->where('b.day' , '2015-12-12');
     })
    ->leftJoin('doctors AS c', 'a.doctor_id', '=', 'c.id')
    ->where('a.doctor_id', $doctorId)
    ->get();

Of course as $doctorId you should set 1 to get exact same result

Ad
source: stackoverflow.com
Ad