Laravel DB query builder with LEFT JOIN using an RAW expression
I would like to left join data from a many-to-many relationship. In my first attempt to solve the problem I ended up with the situation described at https://stackoverflow.com/questions/916414/how-can-a-left-outer-join-return-more-records-than-exist-in-the-left-table
Now I thought that I concatenate the values from the many-to-many relationship before I do the left join, but I'm stuck again and not getting anywhere. I managed to come up with partial solutions, but I don't know how to combine them into the final solution.
The query
SELECT
groups.id as `groups.id`,
GROUP_CONCAT(choices.text) AS choices
FROM `input-choices` choices
LEFT JOIN `input-choice_input-group` pivot ON choices.id = `pivot`.`input-choice_id`
LEFT JOIN `input-groups` groups ON groups.id = `pivot`.`input-group_id`
WHERE groups.id = 2
GROUP BY groups.id
(WHERE groups.id = 2 would finally change to WHERE groups.id = `input-types`.`input-group.id`)
produces e. g. a result set like:
groups.id | choices
2 | 1,2,3,4,5
As next step I I thought it makes sense to left join the result from the previous query with the Query Builder DB::raw() method, but I got stuck again.
$survey = DB::table('questions')
->join('question_survey', function ($join) {
$join->on('questions.id', '=', 'question_survey.question_id')
->where('question_survey.survey_id', '=', 1);
})
->leftJoin('answer_question_survey', 'question_survey.id', '=', 'answer_question_survey.question_survey_id')
->leftJoin('answers', 'answer_question_survey.answer_id', '=', 'answers.id')
->leftJoin('input-types', 'input-types.id', '=', 'questions.input-type_id')
->leftJoin(DB::raw(...), 'input-types.input-group.id', '=', '?.groups.id')
->select(
'questions.id AS question.id',
'questions.text AS question.text',
'questions.explanation AS question.explanation',
'questions.is_required AS question.is_required',
'questions.input-type_id AS question.input-type_id',
'input-types.id AS input-type.id',
'input-types.name AS input-type.name',
'input-types.input-group_id AS input-type.input-group_id',
'question_survey.id AS question_survey.id',
'question_survey.question_id AS question_survey.question_id',
'question_survey.survey_id AS question_survey.survey_id',
'question_survey.parent_id AS question_survey.parent_id',
'answer_question_survey.id AS answer_question_survey.id',
'answer_question_survey.question_survey_id AS answer_question_survey.question_survey_id',
'answer_question_survey.answer_id AS answer_question_survey.answer_id',
'answers.id AS answer.id',
'answers.text AS answer.text'
)
->get();
- How to use the first query inside the
DB::raw()method. I'm always getting errors like "Syntax error or access violation: ..." - How would I complete the join condition
'input-types.input-group.id', '=', '?.groups.id'because I don't know the name of the table I join.
Please or to participate in this conversation.