Salar Salar - 1 year ago 208
SQL Question

Laravel Left join add where clause to right table

If I add a where clause to a left join, that this where clause is working on the right table, I only get those results frome left table that can match the right table.

$notifications = \DB::table('notifications')
->select(\DB::raw("notifications.uuid ,images.local_path as title_image" ))
->leftJoin('images','images.owner_uuid', '=' ,'notifications.uuid')
where('images.relation','=','notification_title') ;

How can I add this where clause to the left join, that does not make this problem?

where('images.relation','=','notification_title') ;

Answer Source

in left join all the where clauses that are going to act on the right table must be added to the JOIN statement itself. use this code

$notifications = \DB::table('notifications')
            ->select(\DB::raw("notifications.uuid , images.local_path as title_image" ))
            ->leftJoin('images',function ($join) {
                $join->on('images.owner_uuid', '=' , 'notifications.uuid') ;
                $join->where('images.relation','=','notification_title') ;