Laravel: How to query a Model Relationship only if it exists

Question

I have a model Survey with a column installer_id which is related to hasOne another model Installation which is related to hasMany another model Assignment.

I want to write a query to fetch all Survey where the installer_id is not null, and where IF an Assignment exists check all the assignments if any status is != 2 ie status = 0 or 1 return the survey.

I tried this query but it does not capture the "if an assignment exists then check the status" part

 $surveys = Survey::whereNotNull('installer_id')
            ->orWhereHas('installation',function ($query) {
                return $query->whereHas('assignments',function ($q){
                    return $q->where('status','!=', 2 );
                });
        })->get();

I also tried to define a hasManyThrough relationship in the model.

    public function assignments()
    {
        return $this->hasManyThrough(Assignment::class,Installation::class);
    }

and then using this query

 $schedulables = Survey::whereNotNull('installer_id')
            ->orWherehas('assignments',function ($query){
                return $query->where('assignments.status','!=', 2 );
            })->get()

Please any suggestions and help would be appreciated

score 0 · Answer 1 · answered Sep 17 '21 at 10:29

0

I think you had the correct approach, however whereNotNull is not necessary if you're already going to check with whereHas, so this way is sufficient I think:

$surveys = Survey::whereHas('installation',function ($query) {
                return $query->whereHas('assignments',function ($q){
                    return $q->where('status','!=', 2 );
                });
        })->get();

and I think you're missing the ->get(); in your code, thats why you were not getting the results

answered Sep 17 '21 at 10:29

Marwane Ezzaze

1,032
5
11

I'm still trying to get all the surveys but if they have an installation which is related to the assignment - assignment status should not be 2. – nellytadi Sep 17 '21 at 10:33
Also, I'm checking if `installer_id` is not null which is a column on the surveys table separate from the `installations` relationship – nellytadi Sep 17 '21 at 10:39

score 0 · Accepted Answer · answered Sep 17 '21 at 14:05

0

Original answer

What i needed was the whereDoesntHave method.

$surveys = Survey::whereNotNull('installer_id')
    ->whereDoesntHave('installation.assignments', fn ($query) => $query->where('status', 2))
    ->get();

answered Sep 17 '21 at 14:05

nellytadi

91
1
15

Laravel: How to query a Model Relationship only if it exists

2 Answers2