John Davidson

Message:

This is about these 4 tables with the following primary keys.

• Courses: CursusNaam varchar


• Exercises: OpdrachtID int


• Exercise progress: OpdrachtVoortgangID int


• Students: LeerlingID int

One course can have many exercises (so exercises has the foreign key CursusNaam).

The progress of the exercises per student is stored in the exercise progress table (so it has the foreign
key OpdrachtID) and of course the exercise progress is linked to a student (so it has the foreign key LeerlingID.

---

What i tried to do is to get and display the latest exercise finished by the student. I did this with an hasManyThrough relation in the course Model.
This works fine when there's only one student in the table exercise progress. However, when i add one more row with a different student in the Exercise progress table, it displays the last OpdrachtVoortgangID from a course to the wrong course!

Exercise progress: This works with one student, it shows right:

Image of view where it shows right

INSERT INTO `opdrachtvoortgang` (`OpdrachtVoortgangID`, `LeerlingID`, `OpdrachtID`, `IsKlaar`, `Beoordeling`) VALUES

(1, 1, 1, b'1', 'Voldoende'),

(2, 1, 2, b'1', 'Onvoldoende'),

(3, 1, 3, b'1', 'Voldoende'),

(4, 1, 4, b'0', 'onvoldoende'),

(5, 1, 5, b'0', 'Voldoende'),

(6, 1, 6, b'0', 'Voldoende');

Exercise progress wrong #1:Adding another student causes OpdrachtVoortgangID:2 (the last one of the course it belongs to) to display to the wrong course:

Image of view where OpdrachtID 2 moved to wrong course

INSERT INTO `opdrachtvoortgang` (`OpdrachtVoortgangID`, `LeerlingID`, `OpdrachtID`, `IsKlaar`, `Beoordeling`) VALUES

(1, 1, 1, b'1', 'Voldoende'),

-- Different student with id 2 messes it up!

(2, 2, 2, b'1', 'Onvoldoende'),

(3, 1, 2, b'1', 'Onvoldoende'),

(4, 1, 3, b'1', 'Voldoende'),

(5, 1, 4, b'0', 'onvoldoende'),

(6, 1, 5, b'0', 'Voldoende'),

(7, 1, 6, b'0', 'Voldoende');

Exercise progress wrong #2: I just found out 'skipping' a primary key number has the same effect as having the progress of another student in between.

(1, 1, 1, b'1', 'Voldoende'),

(3, 1, 2, b'1', 'Onvoldoende'),

(4, 1, 3, b'1', 'Onvoldoende'),

(5, 1, 4, b'1', 'Voldoende'),

(6, 1, 5, b'0', 'onvoldoende'),

(7, 1, 6, b'0', 'Voldoende'), 

Exercise table:

INSERT INTO `opdrachten` (`OpdrachtID`, `Opdracht`, `CursusNaam`, `Deadline`) VALUES

(1, '1', 'Laravel-essentials 1', '2021-02-10 13:52:26'),

(2, '2', 'Laravel-essentials 1', '2021-02-20 13:52:26'),

(3, '1', 'ASPAdvanced', NULL),

(4, '2', 'ASPAdvanced', NULL),

(5, '3', 'ASPAdvanced', NULL),

(6, '3B', 'ASPAdvanced', '2021-03-25 23:42:02');

Here's the course controller:

  $cursussen = Cursus::get();

return view('home')

            ->with(compact('cursussen'))

This is how i display it in the view:

@foreach ($cursussen as $cursus)   

$cursus->getVoortgang()

@endforeach

Here's the Course model:

    //get the latest finished exercise by a student

    function getVoortgang()

    {

        return $this->hasManyThrough('App\Models\OpdrachtVoortgang', 'App\Models\Opdrachten', 'CursusNaam', 'OpdrachtVoortGangID', 'CursusNaam', 'OpdrachtID')

            ->where('LeerlingID', 1) //get progress from student with ID 1

            ->where('IsKlaar', 1) //only exercises that are finished

            ->whereNotNull('IsKlaar' )

            ->orderByDesc('OpdrachtID')

            ->get(); //get instead of first to show that that the progress row got displayed next to the wrong course.

    }

Thanks a lot for having a look.
Edit: This is the output of dd($cursus->getVoortgang())

---

My Answer:
It seems like the issue you are facing is related to the way Laravel handles the hasManyThrough relationship when there are gaps in the primary key sequence or when multiple students are involved.

One possible solution to this issue is to explicitly define the foreign key and local key in the hasManyThrough relationship to ensure that the correct relationships are established.

In your Course model, you can update the getVoortgang function as follows:

php
function getVoortgang()
{
return $this->hasManyThrough('App\Models\OpdrachtVoortgang', 'App\Models\Opdrachten', 'CursusNaam', 'OpdrachtID', 'CursusNaam', 'OpdrachtID')
->where('LeerlingID', 1) //get progress from student with ID 1
->where('IsKlaar', 1) //only exercises that are finished
->whereNotNull('IsKlaar')
->orderByDesc('OpdrachtID')
->get();
}


By explicitly specifying the foreign key ('OpdrachtID') and local key ('OpdrachtID') in the hasManyThrough relationship, you can ensure that the correct relationships are established even when there are gaps in the primary key sequence or multiple students are involved.

I hope this helps resolve the issue you are facing with displaying the latest exercise finished by the student in the correct course. Let me know if you have any further questions or issues.