Laravel hasmanythrough model places row in wrong parent when another student is added. What causes this problem?

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: 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 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.

vincent15000

5 years ago

Level 63

Hello,

Here is an example from the Laravel doc.

class Project extends Model
{
    public function deployments()
    {
        return $this->hasManyThrough(
            Deployment::class,
            Environment::class,
            'project_id', // Foreign key on the environments table...
            'environment_id', // Foreign key on the deployments table...
            'id', // Local key on the projects table...
            'id' // Local key on the environments table...
        );
    }
}

So perhaps I would try this with your code.

return $this->hasManyThrough(
	'App\Models\OpdrachtVoortgang',
	'App\Models\Opdrachten',
	'CursusNaam',
	'OpdrachtID', // =>FOREIGN KEY IN YOUR OpdrachtVoortgang table AND NOT PRIMARY KEY
	'CursusNaam',
	'OpdrachtID')->...

Tell me if it helps ;).

Please or to participate in this conversation.