我使用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();
有没有更好的方法，或者我应该坚持这个方法?
当前回答
在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 ...)
其他回答
代码示例。
首先:
$matchesLcl=[];
数组在这里被填充使用所需的计数/循环条件，增量:
$matchesLcl['pos']= $request->pos;
$matchesLcl['operation']= $operation;
//+......+
$matchesLcl['somethingN']= $valueN;
更进一步，他又用了这样一种简洁的表达:
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'));
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'));
}
}
你可以像这样在匿名函数中使用子查询:
$results = User::where('this', '=', 1)
->where('that', '=', 1)
->where(
function($query) {
return $query
->where('this_too', 'LIKE', '%fake%')
->orWhere('that_too', '=', 1);
})
->get();
使用Array的条件:
$users = User::where([
'column1' => value1,
'column2' => value2,
'column3' => value3
])->get();
将产生如下查询:
SELECT * FROM TABLE WHERE column1 = value1 and column2 = value2 and column3 = value3
使用匿名函数的条件:
$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();
将产生如下查询:
SELECT * FROM TABLE WHERE column1 = value1 and (column2 = value2 or column3 = value3) and (column4 = value4 and column5 = value5)
在这种情况下，你可以使用这样的代码:
User::where('this', '=', 1)
->whereNotNull('created_at')
->whereNotNull('updated_at')
->where(function($query){
return $query
->whereNull('alias')
->orWhere('alias', '=', 'admin');
});
它应该为你提供这样的查询:
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')
