How to Create Multiple Where Clause Query Using Laravel Eloquent?

Question

I'm using the Laravel Eloquent query builder and I have a query where I want a WHERE clause on multiple conditions. It works, but it's not elegant.

Example:

$results = User::where('this', '=', 1)
    ->where('that', '=', 1)
    ->where('this_too', '=', 1)
    ->where('that_too', '=', 1)
    ->where('this_as_well', '=', 1)
    ->where('that_as_well', '=', 1)
    ->where('this_one_too', '=', 1)
    ->where('that_one_too', '=', 1)
    ->where('this_one_as_well', '=', 1)
    ->where('that_one_as_well', '=', 1)
    ->get();

Is there a better way to do this, or should I stick with this method?

Solution 1

In Laravel 5.3 (and still true as of 7.x) you can use more granular wheres passed as an array:

$query->where([
    ['column_1', '=', 'value_1'],
    ['column_2', '<>', 'value_2'],
    [COLUMN, OPERATOR, VALUE],
    ...
])

Personally I haven't found use-case for this over just multiple where calls, but fact is you can use it.

Since June 2014 you can pass an array to where

As long as you want all the wheres use and operator, you can group them this way:

$matchThese = ['field' => 'value', 'another_field' => 'another_value', ...];

// if you need another group of wheres as an alternative:
$orThose = ['yet_another_field' => 'yet_another_value', ...];

Then:

$results = User::where($matchThese)->get();

// with another group
$results = User::where($matchThese)
    ->orWhere($orThose)
    ->get();

The above will result in such query:

SELECT * FROM users
  WHERE (field = value AND another_field = another_value AND ...)
  OR (yet_another_field = yet_another_value AND ...)

Solution 2

Query scopes may help you to let your code more readable.

http://laravel.com/docs/eloquent#query-scopes

Updating this answer with some example:

In your model, create scopes methods like this:

public function scopeActive($query)
{
    return $query->where('active', '=', 1);
}

public function scopeThat($query)
{
    return $query->where('that', '=', 1);
}

Then, you can call this scopes while building your query:

$users = User::active()->that()->get();

Solution 3

You can use subqueries in anonymous function like this:

 $results = User::where('this', '=', 1)
       ->where('that', '=', 1)
       ->where(
           function($query) {
             return $query
                    ->where('this_too', 'LIKE', '%fake%')
                    ->orWhere('that_too', '=', 1);
            })
            ->get();

Solution 4

In this case you could use something like this:

User::where('this', '=', 1)
    ->whereNotNull('created_at')
    ->whereNotNull('updated_at')
    ->where(function($query){
        return $query
        ->whereNull('alias')
        ->orWhere('alias', '=', 'admin');
    });

It should supply you with a query like:

SELECT * FROM `user` 
WHERE `user`.`this` = 1 
    AND `user`.`created_at` IS NOT NULL 
    AND `user`.`updated_at` IS NOT NULL 
    AND (`alias` IS NULL OR `alias` = 'admin')

Solution 5

Conditions using Array:

$users = User::where([
       'column1' => value1,
       'column2' => value2,
       'column3' => value3
])->get();

Will produce query like bellow:

SELECT * FROM TABLE WHERE column1 = value1 and column2 = value2 and column3 = value3

Conditions using Anonymous Function:

$users = User::where('column1', '=', value1)
               ->where(function($query) use ($variable1,$variable2){
                    $query->where('column2','=',$variable1)
                   ->orWhere('column3','=',$variable2);
               })
              ->where(function($query2) use ($variable1,$variable2){
                    $query2->where('column4','=',$variable1)
                   ->where('column5','=',$variable2);
              })->get();

Will produce query like bellow:

SELECT * FROM TABLE WHERE column1 = value1 and (column2 = value2 or column3 = value3) and (column4 = value4 and column5 = value5)

View more solutions