Laravel query builder two tables in left join

Question

I have the following query which I'm trying to convert into Laravel's query builder so I can take advantage of automatic escaping etc.

SELECT subjects.name, report_comments.comment
FROM subjects
LEFT JOIN (report_comments, library_comments) ON subjects.id = library_comments.subject_id
AND report_comments.library_comment_id = library_comments.id
AND report_comments.report_id = 1

Effectively what the query says is 'get the names of all the subjects, and if they have a matching report_comment (via the intermediate library_comments table), return that along with the subject' (a subject has either one or zero report_comments for the given criteria). The query works if I run it directly in MySQL and returns the results I'd expect. The report_comment.report_id = 1 is hard-coded at the moment but will eventually be a placeholder so that any report_id can be passed in.

So far I've managed to get:

DB::table('subjects')->select(['subjects.name', 'report_comments.comment'])->leftJoin('report_comments', function ($join) {
$join->on('subjects.id', '=', 'library_comments.subject_id')
->on('report_comments.library_comment_id', '=', 'library_comments.id')
->on('report_comments.report_id', '=', '1');
})

If I add toSql the result is:

select `subjects`.`name`, `report_comments`.`comment` from `subjects` left join `report_comments` on `subjects`.`id` = `library_comments`.`subject_id` and `report_comments`.`library_comment_id` = `library_comments`.`id` and `report_comments`.`report_id` = `1`

This is almost what I want, except it fails because the library_comments table is not mentioned at all:

Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'library_comments.subject_id' in 'on clause' (SQL: select `subjects`.`name`, `report_comments`.`comment` from `subjects` left join `report_comments` on `subjects`.`id` = `library_comments`.`subject_id` and `report_comments`.`library_comment_id` = `library_comments`.`id` and `report_comments`.`report_id` = `1`)'

What I need to do is tell the leftJoin function about report_comments and library_comments, but there doesn't seem to be any way to do this. I tried:

leftJoin(['report_comments', 'library_comments'], function($join)

on a guess that Laravel might convert an array of table names into (report_comments, library_comments), but that didn't work and gave me the following warning:

PHP Notice:  Array to string conversion in /home/paul/sites/report-assistant/vendor/laravel/framework/src/Illuminate/Database/Grammar.php on line 39

Is there a way to pass multiple tables into leftJoin, or do I need to completely rewrite the query in order to work with Laravel's query builder?

I'm using laravel/framework version 5.8.21 and all my dependencies are up to date (composer update && npm update).

have a look at these they were of help to me with a similar issue [stackoverflow](https://stackoverflow.com/questions/16848987/a-join-with-additional-conditions-using-query-builder-or-eloquent) and [laracast](https://laracasts.com/discuss/channels/laravel/where-clause-in-eloquent-join) — Jelly Bean, Jun 07 '19 at 13:43

Dilip Patel · Answer 1 · 2019-06-08T09:29:15.037

1

Use BD::raw

write query like this and It will work

 DB::table('subjects')->select(['subjects.name, report_comments.comment'])->leftJoin(DB::raw('(report_comments, library_comments)'), function ($join) {
$join->on('subjects.id', '=', 'library_comments.subject_id')
->on('report_comments.library_comment_id', '=', 'library_comments.id')
->on('report_comments.report_id', '=', '1');
})

edited Jun 08 '19 at 09:29

answered Jun 07 '19 at 13:54

Dilip Patel

764
15
24

score 0 · Answer 2 · answered Jun 07 '19 at 14:04

Not sure if this will work but i assume it will be somthing along these lines, hopefully you get something out of it.

Basically added a check to see if the relationship exists if it does then join it.

Subject::select('subjects.name, report_comments.comment')
    ->leftJoin('library_comments', 'subjects.id, '=', library_comments.subject_id')
    ->leftJoin('report_comments', function($join){

          if(report->library->relationship){
              $join->on('report_comments.library_comment_id', '=', 'library_comments.id')
              ->where('report_comments.report_id', '=', '1');
          }

})

score 0 · Accepted Answer · answered Jun 07 '19 at 15:09

After a bit of tinkering, I managed to find the answer in two parts:

First, I had to tweak this part of the join:

on('report_comments.report_id', '=', '1')

and replace it with:

where('report_comments.report_id', '=', '1')

If I didn't do this, Laravel would quote 1 with backticks, causing MySQL to interpret it as a column name.

The other change was to use DB::raw, which I was trying to avoid but I don't think it's too bad in this situation because I'm passing a hardcoded string rather than user input (or anything influenced by user input). The leftJoin now looks like:

leftJoin(DB::raw('(report_comments, library_comments)')

Laravel query builder two tables in left join

3 Answers3