本文翻译自:How to Create Multiple Where Clause Query Using Laravel Eloquent?
I'm using the Laravel Eloquent query builder and I have a query where I want a WHERE
clause on multiple conditions. 我正在使用Laravel Eloquent查询构建器,我有一个查询,我想在多个条件下的WHERE
子句。 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? 有没有更好的方法来做到这一点,还是我应该坚持这种方法?
#1楼
参考:https://stackoom.com/question/1J5Oa/如何使用Laravel-Eloquent创建多个Where子句查询
#2楼
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();
#3楼
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();
#4楼
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. 但是,我从来不需要在查询中使用那么多WHERE子句,它可能表明数据结构存在问题。
It may be helpful for you to learn about data normalization: http://en.wikipedia.org/wiki/Third_normal_form 了解数据规范化可能会有所帮助: http : //en.wikipedia.org/wiki/Third_normal_form
#5楼
In Laravel 5.3 you can use more granular wheres passed as array: 在Laravel 5.3中,您可以使用更多粒度的数组作为数组传递:
$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. 我个人还没有经过短短多次发现用例为这个where
的呼叫,但事实是,你可以使用它。
Since June 2014 you can pass an array to where
自2014年6月起,您可以将数组传递到where
As long as you want all the wheres
use and
operator, you can group them this way: 只要你想要的所有wheres
使用and
运营商,您可以用这种方式它们分组:
$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 ...)
#6楼
$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);
}