There is the useful hasManyThrough in Eloquent in which you can you 3 Tables and define which keys to use to join them.
Laravels example and documentation can be found here .
There is a little detail problem with it, it is all called id and the example wasn’t helping me much.
I have the following tables to handle job applications to jobs
Jobs
– id
– title
– description
Users
– id
– firstname
– lastname
Application
– id
– user_id
– job_id
– application_date
In the jobsmodel I want to get a list of all applicants.
Here is the first version of this
public function applicants(): HasManyThrough
{
return $this->hasManyThrough(
'App\Models\User',
'App\Models\Application',
'job_id',
'id',
'id',
'id'
);
}
While testing I received some strange applicants which never applied for the job.
After debugging I found that the last 'id' uses job.id to load the users with user.id. Bug found.
It should really be application.user_id to load the correct users. So a small change to this:
public function applicants(): HasManyThrough
{
return $this->hasManyThrough(
'App\Models\User',
'App\Models\Application',
'job_id', // (application.job_id)
'id', // Foreign key on User table (user.id)
'id', // Local key on job table (job.id)
'user_id' // Local key on Application table (application.user_id)
);
}
So changing the last id to user_id fixed that bug.