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

nellytadi · Accepted Answer · 2021-09-17 14:05:40Z

1

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, 2021 at 14:05

nellytadi

1012 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marwane Ezzaze · Accepted Answer · 2021-09-17 10:29:09Z

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, 2021 at 10:29

Marwane Ezzaze

1,0776 silver badges12 bronze badges

2 Comments

nellytadi Over a year ago

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 Over a year ago

Also, I'm checking if installer_id is not null which is a column on the surveys table separate from the installations relationship

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related