Nested many-to-many relations

I'm trying to grasp the concept of 'Has-Many-Through' relations.

Here are my current relations,

The student can join multiple clubs.
The club consist of many students.
The club have different positions available and can be assigned to students (i.e President, Chairman..etc)

Here is how my db looks like,

Students
Clubs
club_student (pivot table)
Positions
club_position (pivot table)

From Laravel documentation, my understanding of nested relations is to do this, $students = Student::with('clubs.positions')->get();

My question here is, how do I retrieve all the available information? (Student name, student clubs, student positions & position points in the respective clubs).

Do I need to foreach loop everything?

        foreach ($students as $student) {
            echo $student->name . " -> ";

            foreach ($student->clubs as $club) {
                echo $club->name . " -> ";

                foreach ($club->positions as $position) {
                    echo $position->points . "<br>";
                }
            }
        }

mlewis

7 years ago

Best Answer

Level 10

What's being stored on your club_position and club_student table? It's not obvious where the position is assigned to the student?

If every club has the same positions available (there is a default list you choose from) then I think you have an extra table which is complicating matters.

From your description I would have thought your structure should be more like:

students [id, name]
clubs [id, name]
positions [id, name]
club_students [id, club_id, student_id, position_id, points] with index unique(club_id, student_id, position_id)

You'd then set the relationships on them as follows

students -> hasMany clubs_students
club ->hasMany club_students
positions ->hasMany club_students
club_students ->belongsTo (one for each of them)

I think you could then just load them all as you stated student->with(club_students,club_students.club,club_students.position) for eager loading.

This of course then takes it away from using an actual pivot table, but would simplify your DB, so maybe is not the answer you were looking for?

synergy

7 years ago

Level 4

@MLEWIS - Thank you for replying.

The clubs may or may not have the same default list to choose from. Some positions may or may not overlap.

That is a very good point about overcomplicating though. I may have to rethink this.

Please or to participate in this conversation.