Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tylerclendenin's avatar

Inner Join nested in Outer join using query builder

I am using Laravel 5.5 and want to do a slightly more complicated query. I want to get all the Nodes no matter if they have an Image. Further I don't want to return any records from Images_Nodes if there is no corresponding record in Images.

This is a simplified version of a more complex query.

select
    n.Node_id, i.Image_id
from
    Nodes AS n
    left join Nodes_Images AS n_i
        join Images i ON n_i.Image_id = i.Image_id
    ON n.Node_id = n_i.Node_id

I thought that

DB::table('Nodes AS n')
    ->leftJoin('Nodes_Images AS n_i', function ($join) {
        $join->on('n.node_id', '=', 'n_i.node_id')
            ->join('images AS i', 'n_i.image_id', '=', 'i.image_id');
    })->select('n.node_id', 'i.image_id');

would produce it, but it returns

select
    [n].[node_id], [i].[image_id]
from
    [Nodes] as [n] 
    left join [Nodes_Images] as [n_i] 
    on [n].[node_id] = [n_i].[node_id]

and this

DB::table('Nodes AS n')
    ->leftJoin('Nodes_Images AS n_i', 'n.node_id', '=', 'n_i.node_id')
    ->join('images AS i', 'n_i.image_id', '=', 'i.image_id')
    ->select('n.node_id', 'i.image_id');

produces this, which does not nest the Images join inside the outer join

select
    [n].[node_id], [i].[image_id]
from
    [Nodes] as [n]
    left join [Nodes_Images] as [n_i] on [n].[node_id] = [n_i].[node_id] 
    inner join [images] as [i] on [n_i].[image_id] = [i].[image_id]
0 likes
2 replies
jlrdw's avatar

For a query that is a little complex I just use getPdo() and write regular sql.

Please or to participate in this conversation.