Laravel: How to group a SQL statement in brackets/parenthesis
It is very easy to chain where()
methods together in Laravel like this:
- $posts = Posts::where("category",1)->where("published",true)->where("active",1)->get()
But this will create a long list of AND WHERE
statements (You can also use orWhere for OR WHERE).
But what if you need to group your where statements in brackets (parenthesis)?
If you want something like this (with one part of the WHERE grouped by brackets (parenthesis):
SELECT *
FROM `orders`
WHERE
category = 1
AND (
featured = 1
OR
discounted IS NOT NULL
)
Then you will have to do something called parameter grouping.
Wrap those grouped parts of the where statement in something like this:
- ...
- ->where(function ($query) {
- // put your grouped ->where() method calls here
- }) ...
Real world example:
- DB::table('users')
- ->where('name', '=', 'John')
-
- ->where(function ($query) {
- // Everything within this closure will be grouped together
- $query->where('votes', '>', 100)
- ->orWhere('title', '=', 'Admin');
- })
-
- ->get();
This would output:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
More...
Comments and discussion about How to group a SQL statement in brackets/parenthesis
Found this interesting? Maybe you want to read some more in this series?
Or see other topics in the Laravel language
Blade Templating System
Controllers
Laravel Database Stuff
Eloquent
Routes and Routing
General Laravel Stuff
Laravel Collections
Or see other languages/frameworks:
PHP Laravel Composer Apache CentOS and Linux Stuff WordPress General Webdev and Programming Stuff JavaScriptOr see random questions
How to access the Laravel's route parameters outside of the controller?
What is the max size, by default, of GET variables?
How to set custom HTTP headers in Apache (by editing .htaccess)
How do you access the php://input stream?
Point all requests to one PHP file
How to convert a string to an array of it's characters in PHP?
What is YAGNI?
How to replace values in a string, in JS
What are macros in Laravel?
How to remove .php from the end of URLs with .htaccess