CODEWITHSUNDEEP
phplaraveleloquent
Xerakon
Xerakon

Reputation: 159

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)

Upvotes: 0

Views: 502

Answers (3)

Yves Kipondo
Yves Kipondo

Reputation: 5613

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.

Upvotes: 0

Evangelos Bitsilis
Evangelos Bitsilis

Reputation: 895

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
    }
}

Upvotes: 2

Bulent
Bulent

Reputation: 3411

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.

Upvotes: 0

Related Questions