LEFT JOIN Query Builder and SQL

Hi,

I'm struck on the last step of getting a query to work.

I have an instance of query builder, which I need to LEFT JOIN with an SQL SELECT. It's pretty complex, so I'm trying to avoid pasting it all in here. I will try and simplify.

I have something like:

1, $SQL = 'SELECT * FROM things' (greatly simplified)

2, $query (Query Builder instance)

and I want to end up with something like:

SELECT * FROM ($SQL) AS cal LEFT JOIN ($query) AS x

ON x.datekey = cal.all_days

Any idea how to do this? I think it's probably using: leftJoinSub()

SilenceBringer

3 years ago

Best Answer

Level 55

@phpmick I think laravel have fromSub method to select from subquery

Also you can find some other variants, just google it

For example - https://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder

It's outdated, but should give you an idea (I think the methods works more-less the same)

1 like

phpMick

3 years ago

Level 15

@SilenceBringer

Yes, that's it.

I just got it working like this:

        $builder->fromSub($SQL, 'cal');

        $things =  $builder->leftJoinSub($query, 'x', function ($join) {
            $join->on('x.datekey', '=', 'cal.all_days');
        })->orderBy('cal.all_days')
            ->get();

Please or to participate in this conversation.