php 如何使用 Laravel Eloquent 创建多个 Where 子句查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19325312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Create Multiple Where Clause Query Using Laravel Eloquent?
提问by veksen
I'm using the Laravel Eloquent query builder and I have a query where I want a WHEREclause on multiple conditions. It works, but it's not elegant.
我正在使用 Laravel Eloquent 查询构建器，并且我有一个查询，我想要一个WHERE针对多个条件的子句。它有效，但并不优雅。
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?
有没有更好的方法来做到这一点，还是我应该坚持使用这种方法？
回答by Jarek Tkaczyk
In Laravel 5.3(and still true as of 6.x) you can use more granular wheres passed as array:
在Laravel 5.3（从6.x 开始仍然如此），您可以使用更细粒度的 wheres 作为数组传递：
$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 wherecalls, but fact is you can use it.
就个人而言，我还没有通过多次where调用找到此用例，但事实是您可以使用它。
Since June 2014 you can pass an array to where
自 2014 年 6 月起，您可以将数组传递给 where
As long as you want all the wheresuse andoperator, you can group them this way:
只要你想要所有的wheresuseand操作符，你就可以这样分组：
$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 ...)
回答by Luis Dalmolin
Query scopes may help you to let your code more readable.
查询范围可以帮助您让您的代码更具可读性。
http://laravel.com/docs/eloquent#query-scopes
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();
回答by Juljan
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();
回答by alexglue
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')
回答by srmilon
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 Antonymous 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)
回答by Majbah Habib
Multiple where clauses
多个 where 子句
$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();
回答by Alex Quintero
The whereColumnmethod can be passed an array of multiple conditions. These conditions will be joined using the andoperator.
该whereColumn方法可以传递多个条件的数组。将使用and运算符连接这些条件。
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
有关更多信息，请查看文档https://laravel.com/docs/5.4/queries#where-clauses 的这一部分
回答by DsRaj
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();
回答by adamk
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();
回答by Lim Kean Phang
$projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])
->orwhere([['owner', 'like', '%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])->get();
