Laravel Eloquent Query With OR WHERE Condition

Question

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);

where A and B and C or D, so the id=112 record is successfully passed by condition D. — TsaiKoga
– TsaiKoga, Commented May 1, 2020 at 9:41
so if you want condition A (deleted_at is null) put effect on condition D (where in 112...). u can change your code to where A and (B and C or D) or where (A and B and C) or (A and D) — TsaiKoga
– TsaiKoga, Commented May 1, 2020 at 9:49

Fitzi · Accepted Answer · 2020-05-01 09:58:08Z

2

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.

answered May 1, 2020 at 9:58

Fitzi

1,67211 silver badges19 bronze badges

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

2 Comments

Muhammad Ali Over a year ago

I can not use whereIn because i want to select records created by me + records assigned to me. I have updated my question

Fitzi Over a year ago

@MuhammadAli In this case you need to move the company part out of both closures. The generall idea how to group parameters remains the same though. Did you manage get the results you wanted? If not I can update my answer.

Collectives™ on Stack Overflow

Laravel Eloquent Query With OR WHERE Condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related