How to group a SQL statement in brackets/parenthesis

October 26, 2018

It is very easy to chain where() methods together in Laravel like this:

<?php
$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:

<?php
    ...
   ->where(function ($query) {
            // put your grouped ->where() method calls here
    }) ...

Real world example:

<?php
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')