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?

There are many possibilities in terms of how this could be simplified, but that would require some more realistic code. Can you update the code to be a little more realistic? For example, there are times when multiple `->where(...)` calls can be replaced by a `->whereIn(...)` call, *et cetera*. — jonathanmarvens, Oct 11 '13 at 19:08
The @Jarek Tkaczyk's solution should be the answer, I agree. But I'd prefer your code like builder script for comprehension and maintenance. — Tiefan Ju, Nov 05 '18 at 16:17
Hope this link will be helpful to the future users in-depth multiple where condition example: https://www.scratchcode.io/laravel-multiple-where-conditions-with-example/ — Mayank Dudakiya, Jan 07 '21 at 21:31
laravel requiring the first call to be static but the rest -> is really bad design. — ahnbizcad, Jun 25 '21 at 18:04
@paresh-mangukiya please stop adding spurious tags to old questions. They are not helpful. — miken32, Dec 26 '21 at 22:29
For anyone seeing this after 2022, Laravel's query builder documentation has examples for [Advanced](https://laravel.com/docs/9.x/queries#advanced-where-clauses) and [Basic](https://laravel.com/docs/9.x/queries#basic-where-clauses) WHERE clauses. — BkiD, Dec 21 '22 at 19:06

score 885 · Accepted Answer · edited Jun 02 '20 at 00:50

885

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

edited Jun 02 '20 at 00:50

shmuels

1,039
1
9
22

answered Dec 17 '14 at 09:45

Jarek Tkaczyk

78,987
25
159
157

11

How do you specify the operator? – Styphon Jan 21 '15 at 14:08
9

@Styphon You don't. Currently it work only with `=`. – Jarek Tkaczyk Jan 21 '15 at 15:51
6

@Styphon and what if i want to make: ```WHERE (a IS NOT NULL AND b=1) OR (a IS NULL AND b=2);``` ? – alexglue Jul 30 '15 at 16:47
Is this supported in Laravel 4 or only in 5 and further? – user985366 Aug 31 '16 at 07:48
12

You can also pass an array of conditions like this: ```$users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get();``` – zeros-and-ones Sep 15 '16 at 19:19
No need for "=" . Also there is whereColumn( [ ] ) works perfect . – mercury Sep 27 '16 at 03:48
3

@jarek : How do I include the `whereNotIn` according to your answer with having other `where` cluases? – Kalanka Jan 09 '17 at 05:22
@Kalanka show a paste of your sql, or pseudo-code you'd like to run, because I can't guess what you mean ;) – Jarek Tkaczyk Jan 09 '17 at 07:25
@jarek: assume i have a table of fields as id,name,type,policy and age.here is the query : `$data=$this->model->where('name','=',$name)->where('type','=',$type)->whereNotIn('policy',$policy)->pluck('id')` – Kalanka Jan 10 '17 at 04:41
1

@Kalanka so basically `where([ .. array of conditions like name, type etc])->whereNotIn('policy', $policies)`, nothing fancy here – Jarek Tkaczyk Jan 10 '17 at 05:54
How can I use raw statement with this example? Something like `datetime >= now()` – Edson Horacio Junior Jul 13 '19 at 17:13
Do you can omit using the `COMPARATOR` when is equal? `->where(['status', '1'])` or you can't omit it when using where like that? – Jonatan Lavado Feb 20 '20 at 16:16
Please note that this will only add `AND` in `WHERE` clause. If you need `OR` condition please use `orWhere()` – Md. A. Apu Nov 30 '21 at 15:46
I don't understand why Laravel documentation does not document things like `orWhere`, `orWhereIn` etc? – Carmageddon Apr 06 '23 at 19:24
@JarekTkaczyk, may I ask you to have a look at a Laravel search related question here : https://stackoverflow.com/questions/76485513/laravel-search-with-multiple-keywords-against-multiple-columns-with-the-search ? – Istiaque Ahmed Jun 15 '23 at 22:02

score 134 · Answer 2 · edited Mar 19 '21 at 23:03

134

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();

edited Mar 19 '21 at 23:03

Toby Allen

10,997
11
73
124

answered Jun 20 '14 at 08:07

Juljan

2,391
1
17
20

Could you please link to the documentation for the likes of `orWhere`? I couldnt find it. – Carmageddon Apr 06 '23 at 19:24
Here you are: https://laravel.com/docs/10.x/queries "If you would like to use a "where" clause on your joins, you may use the where and orWhere methods provided by the JoinClause instance..." – Juljan Apr 11 '23 at 11:01

score 122 · Answer 3 · edited Jan 11 '17 at 16:32

122

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();

edited Jan 11 '17 at 16:32

iainn

16,826
9
33
40

answered Oct 11 '13 at 21:06

Luis Dalmolin

3,416
1
18
24

what is the best practice for a condition like this, query->where('start_date' > $startDate) is it still okay to use Scopes? – Buwaneka Kalansuriya Mar 19 '20 at 10:02

score 77 · Answer 4 · edited Mar 16 '22 at 17:10

Conditions using Array:

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

Will produce query like below:

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 below:

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

score 56 · Answer 5 · edited Aug 17 '18 at 17:53

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

score 18 · Answer 6 · edited Mar 19 '21 at 23:00

18

Model::where('column_1','=','value_1')
       ->where('column_2 ','=','value_2')
       ->get();

OR

// If you are looking for equal value then no need to add =
Model::where('column_1','value_1')
        ->where('column_2','value_2')
         ->get();

OR

Model::where(['column_1' => 'value_1',
              'column_2' => 'value_2'])->get();

edited Mar 19 '21 at 23:00

Toby Allen

10,997
11
73
124

answered May 28 '18 at 04:45

DsRaj

2,288
1
16
26

1

Great stuff.. Thanks @DsRaj – Gass Apr 24 '21 at 13:21

Majbah Habib · Answer 7 · 2017-02-06T11:19:56.087

Multiple where clauses

    $query=DB::table('users')
        ->whereRaw("users.id BETWEEN 1003 AND 1004")
        ->whereNotIn('users.id', [1005,1006,1007])
        ->whereIn('users.id',  [1008,1009,1010]);
    $query->where(function($query2) use ($value)
    {
        $query2->where('user_type', 2)
            ->orWhere('value', $value);
    });

   if ($user == 'admin'){
        $query->where('users.user_name', $user);
    }

finally getting the result

    $result = $query->get();

score 10 · Answer 8 · answered Feb 17 '17 at 03:41

The whereColumn method can be passed an array of multiple conditions. These conditions will be joined using the and operator.

Example:

$users = DB::table('users')
            ->whereColumn([
                ['first_name', '=', 'last_name'],
                ['updated_at', '>', 'created_at']
            ])->get();

$users = User::whereColumn([
                ['first_name', '=', 'last_name'],
                ['updated_at', '>', 'created_at']
            ])->get();

For more information check this section of the documentation https://laravel.com/docs/5.4/queries#where-clauses

score 7 · Answer 9 · edited Aug 17 '18 at 17:55

7

$projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
    ['status','<>','Pending'],
    ['status','<>','Not Available']])
->orwhere([['owner', 'like', '%'.$input.'%'],
    ['status','<>','Pending'],
    ['status','<>','Not Available']])->get();

edited Aug 17 '18 at 17:55

Barry

3,303
7
23
42

answered Feb 11 '18 at 06:29

Lim Kean Phang

501
4
6

score 7 · Answer 10 · answered Jan 24 '23 at 09:47

Multiple Where

You may also pass an array of conditions to the where function. Each element of the array should be an array containing the three arguments typically passed to the where method:

Model::where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Multiple Or Where Clauses

If you need to group an "or" condition within parentheses, you may pass a closure as the first argument to the orWhere method:

Model::where('votes', '>', 100)
        ->orWhere(function ($query) {
            $query->where('name', 'abcd')
                ->orWhere(function ($query) {
                    $query->where('name', 'cdef')
                        ->where('votes', '>', 50);
                });
        })
        ->get();

The example above will produce the following SQL:

select * from `users` where `votes` > 100 or (`name` = "abcd" or (`name` = "cdef" and `votes` > 50));

Mutipal where with whereNot and orWhereNot method

The whereNot and orWhereNot methods may be used to negate a given group of query constraints. For example, the following query excludes products that are on clearance or which have a price that is less than ten:

$products = Product::where([
        ['status', '=', '1'],
        ['is_feature', '1']
    ])
        ->whereNot(function ($query) {
            $query->where('clearance', true)
                ->orWhere('price', '<', 10);
        })
        ->get();

score 5 · Answer 11 · edited Mar 26 '16 at 14:39

Be sure to apply any other filters to sub queries, otherwise the or might gather all records.

$query = Activity::whereNotNull('id');
$count = 0;
foreach ($this->Reporter()->get() as $service) {
        $condition = ($count == 0) ? "where" : "orWhere";
        $query->$condition(function ($query) use ($service) {
            $query->where('branch_id', '=', $service->branch_id)
                  ->where('activity_type_id', '=', $service->activity_type_id)
                  ->whereBetween('activity_date_time', [$this->start_date, $this->end_date]);
        });
    $count++;
}
return $query->get();

score 5 · Answer 12 · edited Aug 17 '20 at 21:05

With Eloquent it is easy to create multiple where check:

First: (Use simple where)

$users = User::where('name', $request['name'])
    ->where('surname', $request['surname'])
    ->where('address', $request['address'])
    ...
    ->get();

Second: (Group your where inside an array)

$users = User::where([
    ['name', $request['name']],
    ['surname', $request['surname']],
    ['address', $request['address']],
    ...
])->get();

You can also use conditional (=, <>, etc.) inside where like this:

$users = User::where('name', '=', $request['name'])
    ->where('surname', '=', $request['surname'])
    ->where('address', '<>', $request['address'])
    ...
    ->get();

score 4 · Answer 13 · edited Jan 06 '21 at 10:23

You may use in several ways,

$results = User::where([
    ['column_name1', '=', $value1],
    ['column_name2', '<', $value2],
    ['column_name3', '>', $value3]
])->get();

You can also use like this,

$results = User::orderBy('id','DESC');
$results = $results->where('column1','=', $value1);
$results = $results->where('column2','<',  $value2);
$results = $results->where('column3','>',  $value3);
$results = $results->get();

score 3 · Answer 14 · answered Nov 23 '18 at 09:59

3

As per my suggestion if you are doing filter or searching

then you should go with :

        $results = User::query();
        $results->when($request->that, function ($q) use ($request) {
            $q->where('that', $request->that);
        });
        $results->when($request->this, function ($q) use ($request) {
            $q->where('this', $request->that);
        });
        $results->when($request->this_too, function ($q) use ($request) {
            $q->where('this_too', $request->that);
        });
        $results->get();

answered Nov 23 '18 at 09:59

Dhruv Raval

1,535
1
8
15

does the searching happen in the phpside or sql side? – Mr Mohamed Dec 07 '18 at 13:27
Sql side. Sql query execute as request parameter. ex. if requrst has this param. Then where this = '' where condition added to query. – Dhruv Raval Dec 07 '18 at 15:02

Cristhian Carreño · Answer 15 · 2016-10-25T14:50:45.760

2

You can use eloquent in Laravel 5.3

All results

UserModel::where('id_user', $id_user)
                ->where('estado', 1)
                ->get();

Partial results

UserModel::where('id_user', $id_user)
                    ->where('estado', 1)
                    ->pluck('id_rol');

edited Oct 25 '16 at 14:50

answered Oct 25 '16 at 13:41

Cristhian Carreño

128
1
4

3

How is this different from the question? – veksen Oct 25 '16 at 14:01

score 2 · Answer 16 · answered Mar 15 '18 at 14:08

2

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

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

answered Mar 15 '18 at 14:08

Abhijeet Navgire

683
7
20

score 2 · Answer 17 · answered Jun 13 '18 at 10:14

2

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

answered Jun 13 '18 at 10:14

pardeep

359
1
5
7

score 2 · Answer 18 · answered Mar 13 '19 at 09:38

2

Use This

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

answered Mar 13 '19 at 09:38

Sooraj Thekkepatt

79
9

CodeToLife · Answer 19 · 2020-06-22T14:27:09.703

A sample of code.

Firstly :

$matchesLcl=[];

array gets filled here using desired count / loop of conditions, incremently :

 $matchesLcl['pos']= $request->pos;
$matchesLcl['operation']= $operation;
//+......+
$matchesLcl['somethingN']= $valueN;

and further with eloquents like this shrink expression :

if (!empty($matchesLcl))
    $setLcl= MyModel::select(['a', 'b', 'c', 'd'])
        ->where($matchesLcl)
        ->whereBetween('updated_at', array($newStartDate . ' 00:00:00', $newEndDate . ' 23:59:59'));
else 
    $setLcl= MyModel::select(['a', 'b', 'c', 'd'])
        ->whereBetween('updated_at', array($newStartDate . ' 00:00:00', $newEndDate . ' 23:59:59'));

score 0 · Answer 20 · answered Aug 08 '19 at 10:58

0

Using pure Eloquent, implement it like so. This code returns all logged in users whose accounts are active. $users = \App\User::where('status', 'active')->where('logged_in', true)->get();

answered Aug 08 '19 at 10:58

slimgera00

66
9

score 0 · Answer 21 · answered Jul 02 '20 at 01:20

if your conditionals are like that (matching a single value), a simple more elegant way would be:

$results = User::where([
         'this' => value,
         'that' => value,
         'this_too' => value,
          ...
      ])
    ->get();

but if you need to OR the clauses then make sure for each orWhere() clause you repeat the must meet conditionals.

    $player = Player::where([
            'name' => $name,
            'team_id' => $team_id
        ])
        ->orWhere([
            ['nickname', $nickname],
            ['team_id', $team_id]
        ])

score 0 · Answer 22 · edited Jul 28 '20 at 10:22

We use this instruction to obtain users according to two conditions, type of user classification and user name.

Here we use two conditions for filtering as you type in addition to fetching user information from the profiles table to reduce the number of queries.

$users = $this->user->where([
                    ['name','LIKE','%'.$request->name.'%'],
                    ['trainers_id','=',$request->trainers_id]
                    ])->with('profiles')->paginate(10);

score -1 · Answer 23 · edited Mar 19 '21 at 22:57

You can do it as following, which is the shortest way.

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

score -5 · Answer 24 · answered Aug 10 '15 at 20:00

public function search()
{
    if (isset($_GET) && !empty($_GET))
    {
        $prepareQuery = '';
        foreach ($_GET as $key => $data)
        {
            if ($data)
            {
                $prepareQuery.=$key . ' = "' . $data . '" OR ';
            }
        }
        $query = substr($prepareQuery, 0, -3);
        if ($query)
            $model = Businesses::whereRaw($query)->get();
        else
            $model = Businesses::get();

        return view('pages.search', compact('model', 'model'));
    }
}

This is very vulnerable to SQL injection. – rrrhys Dec 09 '15 at 06:05 — rrrhys, Dec 09 '15 at 06:05

score -7 · Answer 25 · answered Jun 05 '21 at 12:50

In Eloquent you can try this:

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

score -34 · Answer 26 · edited Feb 26 '15 at 16:34

-34

$variable = array('this' => 1,
                    'that' => 1
                    'that' => 1,
                    'this_too' => 1,
                    'that_too' => 1,
                    'this_as_well' => 1,
                    'that_as_well' => 1,
                    'this_one_too' => 1,
                    'that_one_too' => 1,
                    'this_one_as_well' => 1,
                    'that_one_as_well' => 1);

foreach ($variable as $key => $value) {
    User::where($key, '=', $value);
}

edited Feb 26 '15 at 16:34

Abraham Uribe

3,118
7
26
34

answered Feb 26 '15 at 14:12

Yuri Lazo

1
2

This will execute multiple queries. – veksen Mar 04 '15 at 16:00

How to Create Multiple Where Clause Query Using Laravel Eloquent?

26 Answers26

Linked

Related