Laravel HasManyThrough Relation with Where Condition on related table

Question

I want to get count of Verified Doctors of a city based on hospital (where doctor works). I have created hasManythrough relation in City modal and when I'm using this relationship in blade file it gives all doctors (verified and unverified). I only want to get verified doctors. Here is my database structure:

Database

doctors (columns) ---id--name---is_verified--

hospitals columns) ---id--city_id---name---

doctor_hospitals (columns) --id--hospital_id---doctor_id

Relation in City Modal

    public function cityDoctors()
    {
        return $this->hasManyThrough(
            'App\DoctorHospital',
            'App\Hospital',
            'city_id',
            'hospital_id'
        );
    }

In Controller

 $cities=City::with('cityDoctors')->whereHas('cityDoctors')->get();

In blade file I use

   @foreach($cities as $city)
     <li><a href="{{route('typeSearch',['type' => 'city', 'id' => $city->id])}}">
        <strong>{{$city->cityDoctors->count()}}</strong>{{$city->name}}</a>
     </li>
   @endforeach

It show count of All doctors(verified and unverified). How to get only verified doctors of city?

Answer 1

it's like many to many relation more than has many throw.

Anyway, you can do it using whereHas inside the eager loading statement (with):

  $cities = City::with(['cityDoctors' => function ($query) {
            $query->whereHas('doctor', function ($query) {
                $query->where('is_verified', true);
            });
        }])->has('cityDoctors')->get();

Make sure of the relation name between CityDoctor and Doctor models