Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

sheldonscott's avatar

Sorting out a complex(?) relationship

Preamble

I had previously posted a question regarding this but I don't think I was explicit enough to elicit a proper response. Let's try again.

Setting the Stage

I have three tables of data.

grades

| id | name         |
|----|--------------|
| 1  | Kindergarten |
| 2  | Grade 1      |
| 3  | Grade 2      |
| 4  | Grade 3      |
| 5  | Grade 4      |
| 6  | Grade 5      |
| 7  | Grade 6      |
| 8  | Grade 7      |
| 9  | Grade 8      |
| 10 | Grade 9      |
| 11 | Grade 10     |
| 12 | Grade 11     |
| 13 | Grade 12     |

subjects

| id | name                       |
|----|----------------------------|
| 1  | Aboriginal Studies         |
| 2  | Career and Life Management |
| 3  | English Language Arts      |
| 4  | Health                     |
| 5  | Mathematics                |
| 6  | Physical Education         |
| 7  | Science                    |

units

| id | name                                 |
|----|--------------------------------------|
| 1  | Origin and Settlement Patterns       |
| 2  | Political and Economic Organization  |
| 3  | Personal Choices                     |
| 4  | Resource Choices                     |
| 5  | Comprehend - Use Strategies and Cues |
| 6  | Manage - Plan and Focus              |
| 7  | Manage - Select and Process          |
| 8  | Social Responsibility                |
| 9  | The Community                        |
| 10 | Shape and Space: Measurement         |
| 11 | Algebra and Number                   |
| 12 | Trigonometry                         |
| 13 | Probability                          |
| 14 | Gymnastics                           |
| 15 | Magnetism                            |
| 16 | Rocks and Minerals                   |
| 17 | Sky Science                          |

In essence, each grade has many subjects under it, and each subject has many units under it.

I created a pivot/intermediate table for the grade / subject relationship:

| grade_id | subject_id |
|----------|------------|
| 1        | 1          |
| 1        | 2          |
| 2        | 2          |
| 3        | 1          |
| 3        | 2          |
| 3        | 3          |

(etc.)

Within my Grade model, I have defined

public function subjects()
{
    return $this->belongsToMany('App\Subject');
}

My routes file is as follows:

Route::get('/grade/{grade}', function($name) {
    $grade = App\Grade::where('name', $name)->firstOrFail();

    return view('grade')->with('grade', $grade);
}

which allows me to iterate through all of the subjects for each grade:

@foreach ($grade->subjects as $subject)
    {{ $subject->name }}
@endforeach

The Problem

Now that I want to iterate through all of the units associated with a subject I am lost, hung up on the idea that many units belong to a subject that is dependent on the grade.

Here's an example:

If a user selects Grade 6 and then Science, the units that are connected to this are very different than the units that are connected to Kindergarten Science. That said, there are occasions where the units may be the same, like if a user selects Math as the subject the units under many grades may contain Probability, Shape and Space: Measurement, etc.

I created a pivot/intermediate table to relate the subject and unit data together:

| subject_id | unit_id |
|------------|---------|
| 1          | 4       |
| 1          | 2       |
| 2          | 4       |
| 2          | 13      |
| 2          | 15      |
| 3          | 3       |

(etc.)

But how do I define this relationship in Laravel?

How do I say, again as an example, that if Grade X and Subject Y are selected, then show the corresponding Units 1, 2 and 3? Or if Grade A and Subject Y are selected, then show corresponding Units 1, 5, 12, 19 and 37?

0 likes
6 replies
rodolz's avatar

Honestly I don't see any problem on the relationships you established.

Its all about the process and workflow of your webapp I think, i.e:

You got a view with a list of the Grades in which the users selects the Grade he is interested, having this grade_id in your controller you could do this

public function showSubjects($id){
    
    $grade = Grade::findorFail($id);
    //Find the subjects for the Grade selected
    $subjects = $grade->subjects();
    
    //return to a view with grade and subjects
    return view('some view', compact('subjects','grade'));
}

Then repeat the process of showing the subjects, and manage the selected one within a controller

public function showUnits($id){
    $subject = Subject::findorFail($id);

    //Find the units of this subject
    $units = $subject->units();

    return view('some view', compact('units','subject'));
}

Hope you find this helpful.

sheldonscott's avatar

I suppose the problem is that I need to output all of the data ahead of time to a series of three droplists. And that's where it falls apart for me—if I could have any one id for a grade or subject ahead of time, that would be great. But I need all of the permutations output right off the hop.

rodolz's avatar

So you want to output all the data in 3 droplists( Grades , Subjects , Units). Could you give us like an graphical example?

sheldonscott's avatar

Okay, I solved my problem—without using Eloquent. (I suppose there are instances where it just doesn't cut the mustard?)

I left everything as described in my initial post. When it came time to output the units, I resigned myself to using Query Builder. A simple series of joins and selects later, and I have the desired result:

$all_units = DB::table('grade_subject_unit')
    ->join('grades', 'grade_subject_unit.grade_id', 'grades.id')
    ->join('subjects', 'grade_subject_unit.subject_id', 'subjects.id')
    ->join('units', 'grade_subject_unit.unit_id', 'units.id')
    ->select('grades.number as grade_number', 'subjects.name as subject_name', 'units.*')
    ->get();

The grade_subject_unit table referenced above contains the following structure:

| grade_id | subject_id | unit_id |
|----------|------------|---------|
| 1        | 6          | 25      |
| 1        | 6          | 24      |
| 1        | 6          | 26      |
| 2        | 6          | 24      |
| 2        | 6          | 60      |
| 2        | 6          | 31      |

(etc.)

Please or to participate in this conversation.