How do I perform nested joins using Laravel's query builder?

Question

Edit: to clarify, 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, so don't be cute thinking to tell me that my database isn't designed correctly.

I am using Laravel 5.5 and want to do a slightly more complicated 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]

I believe this functionality does not exist and there doesn't seem to be a workaround other than writing raw SQL. I have created a pull request to hopefully rectify that. github.com/laravel/framework/pull/23059 — Tyler Clendenin
– Tyler Clendenin, Commented Feb 7, 2018 at 18:01

Tyler Clendenin · Accepted Answer · 2018-03-15 03:21:35Z

1

This made it into Laravel 5.6.1

answered Mar 15, 2018 at 3:21

Tyler Clendenin

1,4891 gold badge12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I perform nested joins using Laravel's query builder?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related