How to group a SQL statement in brackets/parenthesis

Laravel: How to group a SQL statement in brackets/parenthesis

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

  1. $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:

  1.     ...
  2.    ->where(function ($query) {
  3.             // put your grouped ->where() method calls here
  4.     }) ...

Real world example:

  1. DB::table('users')
  2.             ->where('name', '=', 'John')
  3.  
  4.             ->where(function ($query) {
  5.             // Everything within this closure will be grouped together
  6.                 $query->where('votes', '>', 100)
  7.                       ->orWhere('title', '=', 'Admin');
  8.             })
  9.  
  10.             ->get();

This would output:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

webdevetc profile pic
webdevetc

I am a 29 year old backend web developer from London, mostly focusing on PHP and Laravel lately. This (webdevetc.com) is my blog where I write about some web development topics (PHP, Laravel, Javascript, and some server stuff). contact me here.



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

Or see other languages/frameworks:
PHP Laravel Composer Apache CentOS and Linux Stuff WordPress General Webdev and Programming Stuff JavaScript
Or see random questions

How to fix the MySQL related "Specified key was too long error" error in Laravel

How do you launch the PHP interactive shell?

How to set up an alias to map one directory to another destination

A list of operators on Eloquent's where() method

How to disable file hotlinking in Apache with .htaccess

What is the null coalescing operator in PHP7?

How to add comments in PHP

How to autoload helper files with composer?

How to find the 2nd most common item in an array?

How to generate an array for a dropdown (with ['key' => 'value']) suitable for a dropdown, from a Laravel collection of Eloquent objects?