How to use where clause in Laravel Eloquent? Examples

Hello Artisans.

This post goes into details on how to use where clause in Laravel eloquent? You will get a detailed overview on usage of where clause with Laravel eloquent queries. We will discuss all available where clause methods in Laravel eloquent one by one. I will add raw SQL queries also in each example of Laravel where clause query for better understanding.

So, let's get started with a simple example.

Basic examples of where clauses.

Example 1: Where Clause examples

$users = User::where('email', '[email protected]')->get();

Raw SQL Query

select * from users where email="[email protected];

You can also use other operators like <, >, <=, >=, <>, !=, like, not like, in, not in, between, not between and many more in your where clause with Laravel eloquent. Let's see the following examples.

$users = User::where('likes', '>=', 100)->get();

Raw SQL Query

Select * from users where likes >= 100;

$users = User::where('likes', '<>', 100)->get();

Raw SQL Query

Select * from users where likes <> 100;

$users = User::where('name', 'like', 'C%')->get();

Raw SQL Query

Select * from users where name like 'C%';

Note: If you are comparing two values if is equal or not then you can skip the operator sign.

Pro Tip: If you are checking a value is equal or not, you can do like this:

$users = User::whereEmail('[email protected]')->get();

instead of this:

$users = User::where('email', '[email protected]')->get();

Example 2: orWhere clause examples

In some cases you have chaining where conditions with OR operator instead of AND operator. In this scenario you can use orWhere() method. Let's see some examples.

$users = User::where('likes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Raw SQL Query

Select * from users where likes > 100 OR name = "John";

Another example of chaining AND-OR-AND operator in one query. Remember, in SQL raw queries we have to add parenthesis to separate the OR and AND conditions. We can do in this way in the Laravel eloquent.

$users = User::where('votes', '>', 100)

            ->orWhere(function($query) {

                $query->where('name', 'Mahzaib')

                      ->where('votes', '>', 50);

            })
            ->get();

Raw SQL Query

select * from users where likes > 100 or (name = 'Mahzaib' and likes > 50);

Example 3: whereBetween clause examples

If you wanna check between two values you can do in this way.

$users = User::whereBetween('likes', [1, 100])

->get();

Raw SQL Query

select * from users where likes between 1 and 100;

Example 4: whereNotBetween caluse example

In the same way you can use whereNotBetween clause also if you wanna check if values no between selected values. Let's see an example. 

$users = User::whereNotBetween('likes', [1, 100])
->get();

Raw SQL Query

select * from users where likes not between 1 and 100;

Example 5: whereNull caluse example

You can check easily if value is null with this method. 

$users = User::whereNull('updated_at')

->get();

Raw SQL Query

select * from users where updated_at is null;

Example 6: whereNotNull caluse example

In the same way you can check where value not is null with the help you whereNotNull() method

$users = User::whereNotNull('updated_at')

->get();

Raw SQL Query

select * from users where updated_at is not null;

Advanced examples of where clauses

Let's see some advanced examples of where clause in laravel eloquent query builder.

Example 1: Where exists clause example

The whereExists() method allows you to write "where exists" sql caluse in your laravel eloquent query. To use this method, you have to pass closure which will receive a query builder instance, allowing you to define the query that should be placed inside of the "where exists" clause. Let's see an example now. 

$users = User::whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
                     })

           ->get();

Raw SQL Query

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

Example 2: Subquery where clause example

Some time you comes out in situation where you want to compare value with the sub query result. In this scenario you can use this method to run sub query in your laravel eloquent query builder. 

User::where(function ($query) {
        $query->select('payment_status')
            ->from('payments')
            ->whereColumn('payments.user_id', 'users.id')
            ->orderByDesc('payments.created_at')
            ->limit(1);
    }, 'Pro')->get();

Raw SQL Query

select * from users where (select payment_status from payments where payments.user_id = users.id order by payments.created_at desc limit 1) = Pro;

All Possible Where clause methods in Laravel

Conclusion:

In this detailed tutorial you have learned about the where clauses in Laravel eloquent query builder. You have checked a lot of examples along with SQL queries attched with the each example for better understanding. At the end you have a got a table where i have mentioned all possible where clauses in laravel eloquent. 

I hope, it will help you. 

Happy Coding :)