Looking at best practice for how to construct and maintain many through many relationships

Published 1 month ago by allw

Good Afternoon,

I am a teacher constructing a site for tracking test results, but cannot get my head around the relationships.

I am envisioning a schema that has students these have many results and a test also has many results. Therefore students should have many tests through many results?

Additionally students will have many classes so there is a group_student pivot table.

How would I call principally all student results for a test where group = foobar?

Is there not an easy way to construct multiple nested relationships in laravel or is this unnecessary and I should just be retrieving each set in turn.

I have made a provisional "map" on laravelsd available:

http://www.laravelsd.com/share/zU8Xrs

Any help would be greatly appreciated,

Thanks!

Best Answer (As Selected By allw)
nikos

if you pass parameter to whereHas() like I did above your not only querying existence but existence of relation with additional query that you've passed so i would get results like this:

Result::whereHas('students', function($query) use ($groupId) {
    $query->join('group_student', 'group_student.student_id', '=', 'students.id')
             ->where('group_student.group_id', $groupId);
}):

I think that you can't use whereHas() in callback of whereHas(), so i used join for second relation....

nikos
nikos
1 month ago (6,420 XP)

Why wouldn't result table contain both user_id and test_id? It would been easier then, right ?

allw
allw
1 month ago (4,950 XP)

Oh yeah never thought of that? So then would I be able to just call Groups hasmany results through Students?

allw
allw
1 month ago (4,950 XP)

wait hangon the Student_UPN is the student_id... just named according to local nomenclature

nikos
nikos
1 month ago (6,420 XP)

Yep :)

allw
allw
1 month ago (4,950 XP)

I think the issue is that group is many to many to student but I don't know how to call group has many results through pivot with students

nikos
nikos
1 month ago (6,420 XP)

Like this maybe:

User::whereHas('group', function($query) use ($groupId) {
$query->where('id', $groupId);
});

Here is the docs https://laravel.com/docs/5.5/eloquent-relationships#querying-relationship-existence

nikos
nikos
4 weeks ago (6,420 XP)

Or you can use $groupName instead of the $groupId

allw
allw
4 weeks ago (4,950 XP)

but it is setup like this;

groups-(hasmany)<group_student>(hasmany)-students-(hasmany)<_results>(belongstomany)-tests

So just querying the existence is not going to help because there will always be students with a group because they will always be in multiple groups.

nikos
nikos
4 weeks ago (6,420 XP)

if you pass parameter to whereHas() like I did above your not only querying existence but existence of relation with additional query that you've passed so i would get results like this:

Result::whereHas('students', function($query) use ($groupId) {
    $query->join('group_student', 'group_student.student_id', '=', 'students.id')
             ->where('group_student.group_id', $groupId);
}):

I think that you can't use whereHas() in callback of whereHas(), so i used join for second relation....

allw
allw
3 weeks ago (4,950 XP)

been tinkering with what you gave me and I've come up with:

  $response = Student::whereHas('groups', function ($query) use ($group_id){
      $query->where('group_id', $group_id);
    })->leftjoin('results', function ($join) use ($test_id) {
      $join->on('students.id', '=', 'results.student_id')
           ->where('test_id', $test_id);
    })->orderBy('last_name', 'asc')->get();

Only issue is that the students.id is overwritten by results.student_id, this can't happen because when sending the corrected results back to the database I'll need to rely on both student.id for some things and result.id for others...

any ideas?

Please sign in or create an account to participate in this conversation.