我使用Laravel雄辩的查询构建器,我有一个查询,我想在多个条件上有一个where子句。它能起作用,但并不优雅。

例子:

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

有没有更好的方法,或者我应该坚持这个方法?


当前回答

一定要对子查询应用任何其他过滤器,否则or可能会收集所有记录。

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

其他回答

多个地方

您还可以将一个条件数组传递给where函数。数组的每个元素都应该是一个数组,包含通常传递给where方法的三个参数:

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

多个或Where子句

如果你需要在圆括号内对"or"条件进行分组,你可以将闭包作为第一个参数传递给orWhere方法:

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

上面的例子将产生以下SQL语句:

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

多指针where with whereNot和orWhereNot方法

whereNot和orWhereNot方法可用于对给定的查询约束组求反。例如,以下查询排除清仓或价格小于10的产品:

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

在Laravel 5.3中(在7.x中仍然如此),你可以使用更细粒度的数组:

$query->where([
    ['column_1', '=', 'value_1'],
    ['column_2', '<>', 'value_2'],
    [COLUMN, OPERATOR, VALUE],
    ...
])

就我个人而言,我还没有发现这个用例超过多个where调用,但事实是你可以使用它。

自2014年6月起,您可以将数组传递到where

只要你想要所有的where use和operator,你可以这样分组:

$matchThese = ['field' => 'value', 'another_field' => 'another_value', ...];

// if you need another group of wheres as an alternative:
$orThose = ['yet_another_field' => 'yet_another_value', ...];

然后:

$results = User::where($matchThese)->get();

// with another group
$results = User::where($matchThese)
    ->orWhere($orThose)
    ->get();

以上将导致这样的查询:

SELECT * FROM users
  WHERE (field = value AND another_field = another_value AND ...)
  OR (yet_another_field = yet_another_value AND ...)

如果你的条件是这样的(匹配一个值),一个简单的更优雅的方式是:

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

但如果你需要OR子句,那么确保你对每个OR where()子句重复必须满足条件。

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

whereColumn方法可以传递一个包含多个条件的数组。这些条件将使用and操作符进行连接。

例子:

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

有关更多信息,请查看文档的这一部分 https://laravel.com/docs/5.4/queries#where-clauses

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