How to do a Left Outer join with Laravel?

Question

i want information from one table and if there is matched info from another table that as well.

this my code

 $scoreObject = DB::table('responses')
        ->select('responses.id', 'responses.questions_id', 'responses.answer_id', 'responses.open_answer', 'responses.user_id',  'responses.scan_id',
             'questions.question', 'questions.question_nr', 'questions.type', 'questions.totalsection_id',
            'answers.id as answerID', 'answers.answer', 'answers.questions_id', 'answers.points'
        )
        ->Join('answers as answers', 'responses.answer_id', '=', 'answers.id')
        ->Join('questions as questions', 'answers.questions_id', '=', 'questions.id')
        ->orderBy('questions.id', 'ASC')
        ->where('responses.scan_id', $scanid)
        ->where('responses.user_id', $userid)
        ->groupBy('questions.id')
        ->get();

It returns all responses that have matches with answers (answers.questions_id questions.id'). some responses don't have matched (because there is no responses.answer_id) but i still want the responses info then.

how can i get such a left outer join in laravel ?

You could try specifying the join as being an **left outer join**: `->join('answers as answers', 'responses.answer_id', '=', 'answers.id', 'left outer')`. The last (optional) parameter of the `join` method is `$type`, which when not specified, defaults to the value `inner`. — Bogdan, Feb 12 '15 at 15:48
@Bogdan, indeed that works! if you put it as answer i can mark it as a correct. weird that it's not better documented here http://laravel.com/docs/4.2/queries#joins. — Dave Driesmans, Feb 12 '15 at 20:48
There are some missing (albeit not essential) pieces from the Laravel documentation. But if you like to get your hands dirty there's always the possibility to dive into Laravel's source to get a better understanding on how it works. I'll post the solution as an answer for future reference. — Bogdan, Feb 12 '15 at 20:53

score 77 · Accepted Answer · edited May 23 '17 at 12:32

77

You could try specifying the join as being a left outer join:

->join('answers as answers', 'responses.answer_id', '=', 'answers.id', 'left outer')

The fourth parameter of the join method is $type, which when not specified, defaults to the value inner. But since left join and left outer join are the same thing, you could just use the leftJoin method instead, to make it more readable:

->leftJoin('answers as answers', 'responses.answer_id', '=', 'answers.id')

edited May 23 '17 at 12:32

Community

1
1

answered Feb 12 '15 at 20:58

Bogdan

43,166
12
128
129

3

In a normal join, the '=' is redundant, but when specifying the $type it isn't – Halfacht Oct 18 '19 at 07:25

How to do a Left Outer join with Laravel?

1 Answers1

Linked