CODEWITHSUNDEEP
phpmysqllaravel
Dave Driesmans
Dave Driesmans

Reputation: 819

How to do a Left Outer join with Laravel?

I want information from one table and if there is matched info from another table.

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 match (because there are no responses.answer_id) but I still want the responses info then.

how can I get such a left outer join in Laravel?

Upvotes: 44

Views: 77231

Answers (1)

Bogdan
Bogdan

Reputation: 44546

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')

Upvotes: 81

Related Questions