Using Multiple Where Conditions with Laravel Eloquent

When working with Laravel Eloquent to query the databases usually we need to use at least one where condition and often times we need several.

In Laravel Eloquent, you can add multiple where clauses by chaining multiple calls to the where() method to add AND conditions, or use orWhere() to add OR conditions

Read on for code examples of how you can use multiple basic where() and orWhere() conditions, conditions defined in arrays, complex chains of conditions, and nested conditions.

Chaining where Multiple Times

The simplest way to use apply multiple where conditions using Laravel Eloquent is to make multiple calls to the ->where function on an Eloquent Model instance.

Consider the following example:

$books = Book::where('genre', 'Fantasy')
    ->where('cover_type', 'hardcover')
    ->where('publish_date', '>', '1999-01-01')
    ->get();

Raw MySQL query:

SELECT *
FROM books
WHERE genre = 'Fantasy' AND cover_type = 'hardcover' AND publish_date >= '1999-01-01'

Tip: in order to view raw SQL generated by Laravel Eloquent or Querybuilder read: How to Get Raw SQL Query from Laravel Query Builder or Model

Chaining where and orWhere Multiple Times

Using Laravel Eloquent we can chain one or several orWhere() conditions, similar to chaining where() conditions.

Consider the following example:

// Example 1: chaining `where` and `orWhere`
$books1 = Book::where('genre', 'Fiction')
    ->orWhere('genre', 'Fantasy')
    ->get();

// Example 2: chaining `where` and multiple `orWhere`
$books2 = Book::where('genre', 'Fiction')
    ->orWhere('genre', 'Fantasy')
    ->orWhere('genre', 'Education')
    ->get();

Raw MySQL query generated:

SELECT *
FROM `books`
WHERE `genre` = 'Fiction' OR `genre` = 'Fantasy';

SELECT *
FROM `books`
WHERE `genre` = 'Fiction' OR `genre` = 'Fantasy' OR `genre` = 'Education';

Using an Array to Pass Multiple where (or orWhere) Conditions

In Laravel Eloquent, you can pass multiple conditions as tuples that consist of [column, value] or [column, operator, value]. When no comparison operator is provided, Laravel defaults to using the “=” (equals) operator.

Here are some examples showing how you might use this:

// Example 1: array of conditions with no operators, Laravel will default to '='
$books1 = Book::where([
    ['genre', 'Fantasy'],
    ['cover_type', 'hardcover'],
])->get();

// Example 2: array of conditions with operators: =, != and LIKE
$books2 = Book::where([
    ['genre', '=', 'Fantasy'],
    ['cover_type', '!=', 'paperback'],
    ['author', 'LIKE', '%Tolkien'],
])->get();

// Example 3: array of conditions in a `where` and in an `orWhere`
$books3 = Book::where([
    ['genre', '=', 'Fantasy'],
    ['type', '=', 'book'],
    ['cover_type', '!=', 'paperback'],
    ['author', 'LIKE', '%Tolkien'],
])->orWhere([
    ['genre', '=', 'Fantasy'],
    ['type', '=', 'ebook'],
    ['author', 'LIKE', '%Rowling'],
])->get();

Raw MySQL queries:

# Example 1: array of conditions with no operators, Laravel will default TO '='
SELECT *
FROM `books`
WHERE (`genre` = 'Fantasy' AND `cover_type` = 'hardcover');

# Example 2: array of conditions with operators: =, != and LIKE
SELECT *
FROM `books`
WHERE (`genre` = 'Fantasy' AND `cover_type` != 'paperback' AND `author` LIKE '%Tolkien');

# Example 3: array of conditions in a `where` and in an `orWhere`
SELECT *
FROM `books`
WHERE (`genre` = 'Fantasy' AND `type` = 'book' AND `cover_type` != 'paperback'
AND `author` LIKE '%Tolkien') OR (`genre` = 'Fantasy' AND `type` = 'ebook' AND `author` LIKE '%Rowling');

Logical Grouping of where and orWhere Conditions

Oftentimes, it is necessary to use logical grouping for your conditions, especially when using orWhere. With the correct grouping, we can ensure that MySQL checks the conditions in the correct order of precedence.

To logically group any number of where() and orWhere() conditions in Laravel we need to provide them inside a closure function. Conditions added inside this closure will, as a result, be wrapped in parenthesis in the SQL query.

The following example shows how we can logically group conditions in Laravel:

$books = Book::select('title', 'genre', 'publish_date', 'cover_type')
    ->where(function ($query) {
        $query->where('genre', 'Fantasy')
            ->orWhere('genre', 'Education');
    })
    ->whereYear('publish_date', '>', 2010)
    ->orderBy('publish_date')
    ->get();

Raw MySQL:

SELECT `title`, `genre`, `publish_date`, `cover_type`
FROM `books`
WHERE (`genre` = 'Fantasy' OR `genre` = 'Education')
AND YEAR(`publish_date`) > 2010
ORDER BY `publish_date` ASC

As a comparison have a look at a similar query where the conditions are ungrouped. Note that this should be avoided since it will produce unintended results due to incorrect grouping.

$booksUngrouped = Book::select('title', 'genre', 'publish_date', 'cover_type')
    ->where('genre', 'Fantasy')
    ->orWhere('genre', 'Education')
    ->whereYear('publish_date', '>', 2010)
    ->orderBy('publish_date')
    ->get();

Raw MySQL:

SELECT `title`, `genre`, `publish_date`, `cover_type`
FROM `books`
WHERE `genre` = 'Fantasy'
OR `genre` = 'Education'
AND YEAR(`publish_date`) > 2010
ORDER BY `publish_date` ASC

Results of the Example Query

Grouped Conditions result	Ungrouped Conditions result

As you can see when comparing the difference, the grouped version only shows books after 2010, as intended, whereas the ungrouped version will additionally show books of genre “Fantasy” books of any date. This is due to the ungrouped conditions and can easily lead to unexpected behaviour.

Where Clauses Supported by Laravel

In the table below you can find a summary of the most common where types. Laravel supports many more, and new ones are regularly being added. For a comprehensive overview, refer to the Laravel documentation: basic where clauses and advanced where clauses.

Conclusion

In this blog post, we learned how to add multiple conditions by chaining where() and orWhere() methods, using arrays for multiple conditions, and using logical grouping. Understanding these options enables us to create suitable conditions for a broad range of queries.

Remember to refer to the Laravel documentation for a comprehensive list of available where types. Happy querying!

References

• Basic Where Clauses (Laravel Documentation)
• Advanced Where Clauses (Laravel Documentation)