CODEWITHSUNDEEP
laraveleloquentwhere-clause
Muhammad Ali
Muhammad Ali

Reputation: 15

Laravel Eloquent Query With OR WHERE Condition

I am using Laravel 6 & mysql 7

I have below query

$tasks = Task::where('deleted_at', null)->where('company_id',$company_id);
$tasks = $tasks->where('created_by',$user_id);
$tasks = $tasks->orWhereIn('id',$task_ids);

It generates below raw query when i print it

SELECT * FROM `tasks` WHERE `deleted_at` IS NULL AND  `company_id` = 25 AND `created_by` = 20 OR 
`id` IN(112,...215) ORDER BY `id` DESC

Now Id 112 is deleted but still showing in result, although i have where('deleted_at', null) condition but it is not working

I want to apply all other conditions on $task_ids as well

How this can be achieved with optimized query?

UPDATED: Here is complete scenario

I want to select all records which is created by me or assigned to me. Here is my complete code.

 $tasks = Task::where('deleted_at', null)->where('company_id',$company_id);
 $tasks = $tasks->where('created_by',$user_id);
 $task_ids = TaskUser::where('user_id',$user_id)->pluck('task_id')->all();
 $tasks = $tasks->orWhereIn('id',$task_ids);

Upvotes: 0

Views: 448

Answers (1)

Fitzi
Fitzi

Reputation: 1673

This is because the AND operator has a higher precedence than OR, which basically means that AND "sticks" together more than OR does. You query basically is interpredeted like this:

SELECT * FROM `tasks` 
WHERE 
    (`deleted_at` IS NULL AND  `company_id` = 25 AND `created_by` = 20) 
  OR 
    ( `id` IN(112,...215) )

I am not entirly sure wheter you actually want to OR anything. If you really want to apply all conditions, you just need to change the orWhereIn to a whereIn.

In case you want all not-deleted tasks, that EITHER belong to a company and a auser OR whose id is in the list, you would need to update your query like this:

$tasks = Task::where('deleted_at', null);

$tasks = $tasks->where(function($q) use ($user_id, $task_ids){

    $q->where(function($q2) use ($user_id, $task_ids) {
        $q2->where('created_by',$user_id)
           ->where('company_id',$company_id);
    })
      ->orWhereIn('id',$task_ids);
});

which should result in this query:

SELECT * FROM `tasks` 
WHERE `deleted_at` IS NULL AND ( 
  ( `company_id` = 25 AND `created_by` = 20 )
  OR
  `id` IN(112,...215) 
)

There is actually a chapter about parameter grouping in the excellent laravel documentation as well.

Upvotes: 2

Related Questions