Laravel Eloquent filter record on with query

I have a relationship: User - Task (BelongsToMany).

Tasks table:

Task_user table:

So, task #11 is assigned to both user #1 and #2

Then I create a table to log all the processes when a user performs a task. It is called: appraisals.

Appraisals table:

When the task is done, the percent field is set to 100

Now, I want to get unfinished tasks of each user. Unfinished task means that task doesn't have any appraisals and the percent field is not equal 100.

Here is my code:

$unfinishedTasks = User::with(['tasks' => function ($query) {
    $query->where('end_at', '>=', date('Y-m-d')); // Get unexpired tasks
    $query->doesnthave('appraisals'); // Doesn't have any appraisals
    $query->orWhereHas('appraisals', function ($query) {
        $query->where('percent', '!=', 100); // The percent field is not equal 100.
    });
}])->get();


dd($unfinishedTasks->toArray());

Here is the result:

User #2 only has a task with id #12. What I expect is user #2 should have 2 tasks: #12 and #11 because task #11 is assigned to both user #1 and #2, only user #1 has finished the task, user #2 hasn't.

Snapey

6 years ago

Level 122

you have a couple of AND where statements and an OR where statement. My thoughts are that perhaps these need to be grouped?

(where and where) or where

versus

where and (where or where)

check the docs for Chaining orWhere Clauses After Relationships

Also, if the completion carries significance in your application, I would consider scopes or just differently named relationships

eg by creating incompleteAppraisals relation which is a copy of the appraisals relationship but has the where condition added to it.

kakallatt

6 years ago

Level 4

Thanks Snapey,

However, I think Chaining orWhere Clauses After Relationships isn't the solution for me. I did try it, but the #2 user still inlcudes 1 task.

        $unfinishedTasks = User::with(['tasks' => function ($query) {
            $query->where('end_at', '>=', date('Y-m-d')); // Get unexpired tasks
            $query->where(function ($query) {
                return $query->doesnthave('appraisals')// Doesn't have any appraisals
                ->orWhereHas('appraisals', function ($query) {
                    $query->where('percent', '!=', 100); // The percent field is not equal 100.
                });
            });
        }])->get();

Please or to participate in this conversation.