Level 75
Have you searched for a similar solution, there have been pages of post that start with I have 3 tables
https://laracasts.com/discuss?q=3+tables
Jun 20, 2017
2
Level 3
nested relationship query
I have 3 tables: projects, entries and submissions
relationships:
Project hasMany(Entry:class)
Entry belongsTo(Project:class) and hasMany(Submissions:class) belongsTo(User:class)
Submission belongsTo(Entry:class)
submissions table has a round field (value is 1, 2 or 3)
How can I get a list of all submissions for a given Project, grouped by round and also grouped by user? Ie, I want to output a list like this:
Round 1
User 1: Submission 1
User 1: Submission 2
User 2: Submission 1
User 2: Submission 2
User 3: Submission 1
User 3: Submission 2
Round 2
User 1: Submission 1
User 1: Submission 2
User 2: Submission 1
User 2: Submission 2
User 3: Submission 1
User 3: Submission 2
Round 3
User 1: Submission 1
User 1: Submission 2
User 2: Submission 1
User 2: Submission 2
User 3: Submission 1
User 3: Submission 2
I tried the following, but it's not working. If I do a dd($allSubmissions) I get an error:
Property [submissions] does not exist on this collection instance. I think I understand why this error is popping up, but not really. Can you explain?
$project = Project::with('entries.submissions')->findOrFail($id);
$allSubmissions = $project->entries->submissions;
dd($allSubmissions)
// filter submissions
$submissions['r1'] = $allSubmissions->where('round',1)->filter(function ($submission, $key) use ($id) {
return $submission->entry->project_id == $id;
});
$submissions['r2'] = $allSubmissions->where('round',2)->filter(function ($submission, $key) use ($id) {
return $submission->entry->project_id == $id;
});
$submissions['r3'] = $allSubmissions->where('round',3)->filter(function ($submission, $key) use ($id) {
return $submission->entry->project_id == $id;
});
How can I better write this query?
Does my db design need improvement?
Thank you very much!
Level 3
I worked out the solution. Laravel has of course provided a simple way to do this: the hasManyThrough() relationship
Project model:
public function submissions()
{
return $this->hasManyThrough(Submission::class, Entry::class);
}
Get all submissions on a Project through Entry
$project = Project::findOrFail($id);
$allSubmissions = $project->submissions()->with('entry')->get();
// group by round # and name key with round #
$rounds = $allSubmissions->groupBy(function ($submission, $key) {
return $submission['round'];
});
// group submissions in each round by username and name key by username
foreach($rounds as $key => $submissionsInRound)
{
$rounds[$key] = $submissionsInRound->groupBy(function ($submission, $key) {
return $submission->entry->user->profile->username;
});
// or just $rounds[$key] = $submissionsInRound->groupBy('username')
}
Please or to participate in this conversation.