How to Create Multiple Where Clause Query Using La

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?

Answer 1

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

Answer 2

You can use subqueries in anonymous function like this:

 $results = User::where('this', '=', 1)
            ->where('that', '=', 1)
            ->where(function($query) {
                /** @var $query Illuminate\Database\Query\Builder  */
                return $query->where('this_too', 'LIKE', '%fake%')
                    ->orWhere('that_too', '=', 1);
            })
            ->get();

Answer 3

Without a real example, it is difficult to make a recommendation. However, I've never needed to use that many WHERE clauses in a query and it may indicate a problem with the structure of your data.

It may be helpful for you to learn about data normalization: http://en.wikipedia.org/wiki/Third_normal_form

Answer 4

use whereIn condition and pass the array

$array = [1008,1009,1010];

User::whereIn('users.id', $array)->get();

Answer 5

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')

Answer 6

You can use array in where clause as shown in below.

$result=DB::table('users')->where(array(
'column1' => value1,
'column2' => value2,
'column3' => value3))
->get();