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

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)

CodePudding user response：

The error message and the documentation are clear. hasManyThrough relation works only with one to many relations

Practices -> Locations is one to many but Locations -> Doctors is many to many there is a pivot table between Locations and Doctors. therefore you can't use hasManyThrough in this scenario

if you really want to use hasManyThrough, you must convert the Locations -> Doctors relation to one to many by placing location_id in the doctors table

Otherwise the only way the get the doctors is like @Bulent suggested

$practice = Practice::where("id", your_id )->with('locations.doctors')->first();

and then iterate thought locations and doctors

foreach ($practice->locations as $location) {
    foreach ($location->doctors as $doctor) {
        // do stuff with each doctor here
    }
}

CodePudding user response：

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.