Eloquent query not returning the expected results from whereHas

Question

Current relationships:

• A Clinic hasMany Consultants
• A Consultant belongsToMany Treatments (pivot table relationship)

A clinic may not have consultants, and a consultant may not have treatments. I only want to return clinics that has consultants, which has treatments, that includes a treatment that matches $treatment_id (hence the has existence check).

Current attempt (which returns 0 results):

clinic::whereHas('consultants.treatments', function ($query) use ($treatment_id) {
                $query->where('treatment_id', $treatment_id);
            })
        ->paginate(10);

I feel I require a whereHas within the whereHas function, to firstly determine if a consultant has treatments, but, if this logic is correct, how can I approach it within the function.

I am expecting 3 results to be returned, but currently it is 0.

How can I achieve this? Many thanks,.

Answer 1

Since it's many to many, it should be:

Clinic::whereHas('consultants.treatments', function ($query) use ($treatment_id) {
        $query->where('id', $treatment_id);
    })
    ->paginate(10);