Laravel orWhere: how to use and group queries
Oct 22, 2022
In Laravel we can use the orWhere
method to add an or
SQL statement in our queries.
The basic usage is pretty simple but it can get confusing when we need to group multiple conditions.
In this article, I will show you how to use it so you don't run into any unexpected bugs in the future.
Basic usage
Let's say for example that I have a database of employees, and I want to get every employee that is either older than 56 years old or younger than 24 years.
You can do that very easily using the orWhere
in your eloquent model
$employees = Employee::where('age', '>', 56)
->orWhere('age', '<', 24)
->get();
And done, we have our employees. Pretty easy right?
Grouping conditions
Now let's say that we not only need to filter by age like above, but we only want full time employees, not contractors or part time.
So, our condition would look something like this:
// Can you see the error?
$employees = Employee::where('age', '>', 56)
->orWhere('age', '<', 24)
->where('type', 'full time')
->get();
This is an error. If we look at the generated query in SQL we can spot the error more easily:
select * from employees where age > 56 or age < 24 and type = 'full time';
With this query, we get the employees older than 56 (it doesn't matter if they are contractors or part time) and employees younger than 24 AND are full time employees.
So our database interprets the query like this:
SELECT * FROM employees WHERE age > 56 OR (age < 24 AND type = 'full time'):
Notice that the last two conditions are grouped. That is because when we add an and
statement in SQL it will be grouped with the previous statement, in this case, the age < 24
.
Instead what we want is this:
SELECT * FROM employees WHERE (age > 56 OR age < 24) AND type = 'full time':
We want both of our age conditions to be grouped. We can do this in Laravel by passing a closure to the where clause:
$employees = Employee::where(function ($query) {
$query->where('age', '>', 56)
->orWhere('age', '<', 24);
})->where('type', 'full time')->get();
Grouping with orWhere
We can do the same type of grouping using the orWhere
method instead. For example, let's say that we want to get all full time employees and part type employees but only if they are older than 44.
We can use orWhere
grouping like this:
$employees = Employee::where('age', '>', 56)
->where('type', 'full time')
->orWhere(function ($query) {
$query->where('type', 'part time')
->where('age', '>', 44);
})->get();
This way our generated query will look like this:
SELECT * FROM employees WHERE type = 'full time' OR (type = 'part time' AND 'age' > 44);
Also, remember those where
clauses inside the orWhere
can also have subgroups so you can make even more complicated queries.
And that is it, that's how you use orWhere
in Laravel with grouping.