'Create Laravel Relationship through Many-To-One and Many-To-Many

I'm having trouble defining a relationship between several models. In the Laravel documentation, I was able to find a hasManyThrough() relationship where the relationship was extended down through two many-to-one models, but I'm having issues with mine from a many-to-one then many-to-many relationship. Here are the tables:

practices
  id
  name

locations
  id
  practice_id
  name

doctors
  id
  name

doctor_location
  doctor_id
  location_id

As you can see, practices can have many locations, and locations can only belong to one practice. Doctors can have many locations, and locations can have many doctors. I'm not certain how to drill down to create a $practice->doctors relationship, however. I did attempt the hasManyThrough() relationship in the practices model:

public function doctors() {
  return $this->hasManyThrough(Doctor::class, Location::class);
}

but this simply threw an sqlstate error looking for a location_id in the doctors table. Is it possible to relate these models?

Edit: SQLState error

Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'doctors.location_id' in 'on clause' (SQL: select `doctors`.*, `locations`.`practice_id` as `laravel_through_key` from `doctors` inner join `locations` on `locations`.`id` = `doctors`.`location_id` where `locations`.`practice_id` = 1)
phplaraveleloquent


Solution 1:[1]

I think you don't need any relationship between doctors and practice in your case. The following query should work:

Practice::with('locations.doctors');

The output should be an array of locations, and each location will contain doctors.

Solution 2:[2]

As you can see in the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'doctors.location_id'

The query which is executed is trying to perform a join between doctors and locations tables

select `doctors`.*, `locations`.`practice_id` as `laravel_through_key` from `doctors` inner join `locations` on `locations`.`id` = `doctors`.`location_id` where `locations`.`practice_id` = 1)

The query use this clause to perform the join

inner join `locations` on `locations`.`id` = `doctors`.`location_id`

As you doctors table doesn't have a location_id attribute this is the main cause of that error.

This error is because you have have define the hasManyThrough relation using wrong models, It should be like this

public function doctors() {
    return $this->hasManyThrough(Doctor::class, DoctorLocation::class);
}

This supposed you have already define a model DoctorLocation which refered to the doctor_location table. This will allow you to access doctors from from Practice

This will allow eloquent to perform the join between Practice and Doctor by passing through the doctor_location table as it has location_id and doctor_id attributes.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bulent
Solution 2 Yves Kipondo

Related Questions