Eloquent: check if relationship has certain conditions

I have a model Survey which is related to hasOne another model Installation which is related to hasMany another model Assignment.
So i defined a hasManyThrough relationship like this

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

I want to write a query to fetch any Survey where the Assignments associated with the survey does not have an assignment.type of 0,1,2,3 and 4.

i.e.
Each survey should have an 5 assignments with records

Survey => [
[Assignment => [type = 0]]
[Assignment => [type = 1]]
[Assignment => [type = 2]]
[Assignment => [type = 3]]
[Assignment => [type = 4]]
]

I tried this query
 $schedulables = Survey::whereNotNull('installer_id')
            ->where(function ($query) {
                $query
                    ->whereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 1');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 2');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 3');
                    })
                    ->orwhereNotExists(function ($query) {
                        return $query->raw('SELECT * FROM assignments,installations where assignments.installation_id = installations.id and installations.survey_id = surveys.id and assignments.type= 4');
                    });

            })
            ->with('customer', 'installer', 'installation')
            ->latest('updated_at')->get();

Please any suggestions and help would be appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If I’ve understood your question correctly, you can use whereHas() and whereNotIn() to achieve what you’re after:

$schedulables = Survey
    ::with('customer', 'installer', 'installation')
    ->whereNotNull('installer_id')
    ->whereHas('assignments', function ($query) {
        $query->whereNotIn('type', [0, 1, 2, 3, 4]);
    })
    ->latest('updated_at')
    ->get();

Method 2

My final solution was to create these relationships in the Survey model

 public function assignment_outdoor(){
        return $this->hasManyThrough(Assignment::class,Installation::class)
            ->where('assignments.type',1)
            ->where('assignments.status',2);
    }
    public function assignment_indoor(){
        return $this->hasManyThrough(Assignment::class,Installation::class)
            ->where('assignments.type',2)
            ->where('assignments.status',2);
    }
    public function assignment_testing_activation(){
        return $this->hasManyThrough(Assignment::class,Installation::class)
            ->where('assignments.type',3)
            ->where('assignments.status',2);
    }
    public function assignment_lm_splicing(){
        return $this->hasManyThrough(Assignment::class,Installation::class)
            ->where('assignments.type',4)
            ->where('assignments.status',2);
    }

And then built the query using doesntHave and ordoesntHave.
$schedulables = Survey::when($installer_filter, function ($query, $installer_filter) {
            return $query->where('installer_id', $installer_filter);
        })
            ->whereNotNull('installer_id')
//actual solution
            ->where(function ($query) {
                return $query

                    ->doesntHave('assignment_outdoor')
                    ->orDoesntHave('assignment_indoor')
                    ->orDoesntHave('assignment_testing_activation')
                    ->orDoesntHave('assignment_lm_splicing');

            })
//end
            ->doesntHave('network_job_order_customer')
            ->with('customer', 'installer', 'installation')
            ->latest('updated_at');


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x