How to Use Where() in Laravel

Laravel has a powerful database query builder that is called Eloquent. It can help build very complicated SQL queries to the database. The most frequent operation that developers use is WHERE and its combinations with different operators.

Eloquent has the where() method that allows doing this. In this article, we will dive into how to use where in Laravel and how to get combined conditions with different operators and braces.

Table of Contents

How to Use Where() in Laravel

I will use the Article model for all examples in this article. This is a regular model extended from Illuminate\Database\Eloquent\Model that can work with the articles table. The table has columns title, content, author, created_at, and updated_at.

Let’s have a look at the possible arguments and usages of the where() method:

where(string $field, string $value);
where(string $field, string $operator, string $value);
where(string $field, string $operator, string $value, string $boolean);
where(array $condition);
where(array $condition, null, null, string $boolean);
where(Closure $callable);
whereFieldName($value)
whereFieldNameOperatorFieldName($value, $value)

The first way is the simplest. You just set the field and value as arguments. Builder will use the operator ‘=’. If you want to specify the operator, you can use the second way. The last argument $boolean can be used to determine how to combine the current where clause with the previous one. Also, you can write a whole condition using an array. The sequence of elements in the array is the same as where() method arguments. If the first parameter of the where() method is an array, Eloquent uses this code to parse its values:

$query->{$method}(...array_values($value));

It means that all values will be passed as arguments to the where function. If you want to group a few where clauses, you can use closure syntax and determine grouped where clauses in the closure.

Finally, Laravel supports magic methods for Eloquent. You can write the field name directly in the method name after the word “where”. Eloquent will understand it and convert it into a correct SQL query.

In this article, I will show examples of all cases described above. But the standard way is using where($value, $field) or where($value, $operator, $field) for simple queries and closure for grouping. Don’t use array syntax if you don’t need it. It is less readable and may be less understandable to other developers.

1. Simple Where

Let’s fetch all articles which have the title “Test title”. The SQL query will look like this:

SELECT * FROM articles WHERE title = 'Test Title'

It is easy. You can call the static where() method directly in the model. Or you can use the static query() method to get the Eloquent builder instance, and call where() for it. In this case, the code will look mere structured:

$articles = Article::query()
    ->where("title", "Test Title")
    ->get();

You can write this in several different ways. For example, you can specify the operator:

$articles = Article::query()
    ->where("title", "=", "Test Title")
    ->get();

If you want to use the LIKE or ILIKE operator instead of equals to check that the field contains some string, use the following code:

$articles = Article::query()
    ->where("title", "like", "%Title%")
    ->get();

Don’t forget, that the LIKE operator expects wildcard with %% symbols. You can do the same thing using an array:

$articles = Article::query()
    ->where(["title", "like", "%Title%"])
    ->get();

If you want to compare strings case-insensitively, use the ILIKE operator instead of LIKE:

$articles = Article::query()
    ->where(["title", "ilike", "%title%"])
    ->get();

Now you know how to use eloquent where like. Eloquent also has magic methods for where(). You can write the filed name in camel case notation after where and Laravel will understand it. For example:

$articles = Article::query()
    ->whereTitle("Test Title")
    ->get();

2. Where AND Where

Here, let’s fetch all articles that have a title with the text “Test Title” and have been created by a user with the name John. The SQL query will look like this:

SELECT * FROM articles WHERE title = 'Test title' AND author = 'John'

If you want to combine two or more where clauses with the operator AND, you can just call the where() method multiple times:

$articles = Article::query()
    ->where("title", "=", "Test title")
    ->where("author", "=", "John")
    ->get();

Also, you can do the same thing using an array:

$articles = Article::query()
  ->where([
      ['title', '=', 'Test title'],
      ['author', '=', 'John'],
  ])
  ->get();

Now you know how to make queries where and where Laravel.

3. Where OR Where

Here, let’s fetch all articles that have a title with the text “Test Title” or “Not Test Title”. Here is the SQL query:

SELECT * FROM articles WHERE title = 'Test Title' OR title = 'Not Test Title'

The simplest way to do this is to use the orWhere() method:

$articles = Article::query()
    ->where("title", "=", "Test Title")
    ->orWhere("title", "=", "Not Test Title")
    ->get();

Also, you can use simple where() and set “or” as the value of the fourth argument:

$articles = Article::query()
    ->where("title", "=", "Test Title")
    ->where("title", "=", "Not Test Title", "or")
    ->get();

You can use Eloquent magic to make this in one line:

$articles = Article::query()
    ->whereTitleOrTitle("Test Title", "Not Test Title")
    ->get();

An array syntax will look like this:

$articles = Article::query()
  ->where([
      ['title', '=', 'Test Title'],
      ['title', '=', 'Not Test Title', 'or'],
  ])
  ->get();

4. Where AND (Where OR Where)

Let’s fetch articles that have a title with the text “Test Title” or “Not Test Title” and which was created by the author John. Here is the SQL query:

SELECT * FROM articles WHERE (title = 'Test Title' OR title = 'Not Test Title') AND author = 'John'

The most convenient way to do this is by using closure:

use Illuminate\Contracts\Database\Eloquent\Builder;

//...

Article::query()
    ->where("author", "John")
    ->where(function (Builder $query) {
        $query->where("title", "=", "Test Title");
        $query->orWhere("title", "=", "Not Test Title");
    })
    ->get();

You can add as many queries as you need to complete the required grouping. Otherwise, you can use an array syntax:

Article::query()
   ->where([
       ['author', '=', 'John'],
       [[
            ['title', '=', 'Test Title'],
            ['title', '=', 'Not Test Title', 'or'],
       ]]
   ])
   ->get();

5. Two AND Where With OR

If you want to combine two AND where clauses by OR operator using array syntax you can set the boolean parameter for the second group. SQL:

SELECT * FROM articles WHERE (author = 'Jane' AND title = 'Test Title' ) OR (author = 'John' AND  title = 'Not Test Title')

Eloquent code:

Article::query()
   ->where([
       [[
            ['author', '=', 'Jane'],
            ['title', '=', 'Test Title'],

       ]],
       [[
            ['author', '=', 'John'],
            ['title', '=', 'Not Test Title', 'and'],
       ], null, null, 'or']
   ])
   ->get();

The where function Laravel syntax looks prettier and more understandable:

use Illuminate\Contracts\Database\Eloquent\Builder;

//...

Article::query()
   ->where(function(Builder $query){
      $query->where('author', 'Jane')
          ->where('title', 'Test Title');
   })
   ->orWhere(function(Builder $query){
      $query->where('author', 'John')
           ->where('title', 'Not Test Title');
   })
   ->get();

This is all that you should know about how to combine where() in Laravel.

6. Where IN

If you need to select articles with certain identifiers, you may want to write a dedicated where clause for each identifier, but there is a simpler way. You can use IN operator:

SELECT * FROM articles WHERE id IN (1, 2, 3, 4, 5, 6);

Eloquent Builder has a whereIn() method that allows making queries like this:

$articles = Article::query()
    ->whereIn("id", [1, 2, 3, 4, 5, 6])
    ->get();

7. Where With Dates

When it comes to dates, things get tricky. The updated_at and created_at fields are stored in the database as timestamps. So you can’t use the equals operator “=” to select records that were created on a specific day. You should use BETWEEN operator or add two WHERE clauses that check the top and bottom bound. For example, let’s select articles that were created on 2022-10-01:

SELECT * FROM articles WHERE created_at BETWEEN '2022-10-01 00:00:00' AND '2022-10-01 23:59:59'

It will work because BETWEEN operator includes bounds. But you can write it manually:

SELECT * FROM articles WHERE created_at >= '2022-10-01 00:00:00' AND created_at <= '2022-10-01 23:59:59'

You can use this code to build a BETWEEN SQL query:

$articles = Article::query()
    ->whereBetween("created_at", ["2022-10-01 00:00:00", "2022-10-01 23:59:59"])
    ->get();

Another way – use a raw SQL query to convert the field into a date and compare your date with the result. But Laravel has a more convenient way to interact with dates. It has these methods:

whereDate(string $field, string $date) – checks that the timestamp belongs to the specified date.
whereDay(string $field, int $dayOfMonth) – checks that the timestamp belongs to the specified day.
whereMonth(string $field, int $month) – checks that the timestamp belongs to the specified month.
whereYear(string $field, int $year) – checks that the timestamp belongs to the specified year.
whereTime(string $field, string $time) – checks that the timestamp belongs to the specified time.

Let’s look at the examples. This code selects all articles that were created on 2022-10-01:

$articles = Article::query()
    ->whereDate("created_at", "2022-10-01")
    ->get();

You can use DateTime or Carbon instance as an argument too:

$articles = Article::query()
    ->whereDate("created_at", Carbon::yesterday())
    ->get();

If you want to fetch only articles that were created in 2022, use this code:

$articles = Article::query()
    ->whereYear("created_at", 2022)
    ->get();

8. Where With NOT

Most of all where methods have variants with th not_ suffix. It allows adding the NOT operator before the where condition. For example, if you want to fetch articles which were created not by John you can use this SQL query:

SELECT * FROM articles WHERE NOT author = 'John'

To build this query using Eloquent use this code:

$articles = Article::query()
    ->whereNot("author", "John")
    ->get();

9. Where With Null

If you want to check if the value of the field is null or not, use a special SQL query:

SELECT * FROM articles WHERE NOT author IS NULL SELECT * FROM articles WHERE NOT author NOT NULL

Laravel Eloquent has special methods which can help do this. It are whereNull() and whereNotNull() methods. For example, let’s find articles without the author:

$articles = Article::query()
    ->whereNull("author")
    ->get();

Here is the code for where is not null Laravel query:

$articles = Article::query()
    ->whereNotNull("author")
    ->get();

Wrapping Up

In this article, I have explained how to use where in Laravel to build complicated SQL queries. If you are interested in writing raw queries, read the article How to Run Raw SQL Queries. As you can see Laravel has a powerful database query builder and you don’t need to use raw queries in most cases. Do you know any useful tips for the where() method in Laravel? Share them in the comments section below.

How to Use Where() in Laravel

How to Use Where() in Laravel

1. Simple Where

2. Where AND Where

3. Where OR Where

4. Where AND (Where OR Where)

5. Two AND Where With OR

6. Where IN

7. Where With Dates

8. Where With NOT

9. Where With Null

Wrapping Up

Your Reaction

Leave a Comment Cancel reply

How to Use Where() in Laravel

1. Simple Where

2. Where AND Where

3. Where OR Where

4. Where AND (Where OR Where)

5. Two AND Where With OR

6. Where IN

7. Where With Dates

8. Where With NOT

9. Where With Null

Wrapping Up

Related posts

Your Reaction

Leave a Comment Cancel reply