Save a new record through a pivot table with three foreign keys in Laravel with Eloquent
I created the database design https://i.stack.imgur.com/PGOy9.png and I'm a bit concerned about the right-side part, the surveys,questions, answers and answer_question_survey tables.
What I want to accomplish
Scenario 1: List all questions with their related answers.
That should be possible with e. g.:
$project = Project::with([
'surveys' => function ($query) {
$query->where('name', 'project-contract');
},
'surveys.questions' => function ($query) {
// no constraint
},
'surveys.questions.answers' => function ($query) {
// no constraint
// answers uses a hasManyThrough relation towards the questions model.
}
])->where('id', 1)->get()->toArray();
Scenario 2: Answer a specific question.
This is the use case where I can't find a proper solution. First I would like to give an answer, e. g.:
$answer = new Answer();
$answer->answer = "I am an answer.";
$answer->save();
In a second step attach the answer to the related question in the related survey. Here I can't figure out a proper solution. Maybe my database design approach is bad. If so, please share your ideas for an optimization. Maybe the way I would like to realize scenario 2 is not good. Again, please share your ideas if you think that this is the case.
If the design is fine, please share your ideas to accomplish the second scenario.
Please or to participate in this conversation.