How to Create Multiple Where Clause Query Using Laravel Eloquent?
PhpLaravelEloquentLaravel Query-BuilderPhp Problem Overview
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?
Php Solutions
Solution 1 - Php
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 - Php
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 - Php
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 - Php
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)
Solution 5 - Php
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 6 - Php
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();
Solution 7 - Php
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();
Solution 8 - Php
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
Solution 9 - Php
$projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])
->orwhere([['owner', 'like', '%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])->get();
Solution 10 - Php
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();
Solution 11 - Php
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();
Solution 12 - Php
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');
Solution 13 - Php
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();
Solution 14 - Php
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();
Solution 15 - Php
You can use array in where clause as shown in below.
$result=DB::table('users')->where(array(
'column1' => value1,
'column2' => value2,
'column3' => value3))
->get();
Solution 16 - Php
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
Solution 17 - Php
Use This
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
Solution 18 - Php
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'));
Solution 19 - Php
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();
Solution 20 - Php
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]
])
Solution 21 - Php
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);
Solution 22 - Php
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();
Solution 23 - Php
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();
Solution 24 - Php
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'));
}
}
Solution 25 - Php
$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);
}