我使用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子句
$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);
}
终于得到了结果
$result = $query->get();
在这种情况下，你可以使用这样的代码:
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')
使用Eloquent很容易创建多个where检查:
首先:(使用简单的where)
$users = User::where('name', $request['name'])
->where('surname', $request['surname'])
->where('address', $request['address'])
...
->get();
第二个:(在数组中分组where)
$users = User::where([
['name', $request['name']],
['surname', $request['surname']],
['address', $request['address']],
...
])->get();
你也可以在里面使用条件(=，<>，等等)，就像这样:
$users = User::where('name', '=', $request['name'])
->where('surname', '=', $request['surname'])
->where('address', '<>', $request['address'])
...
->get();
你可以在Laravel 5.3中使用eloquent
所有的结果
UserModel::where('id_user', $id_user)
->where('estado', 1)
->get();
部分结果
UserModel::where('id_user', $id_user)
->where('estado', 1)
->pluck('id_rol');
根据我的建议，如果你正在做筛选或搜索
那么你应该选择:
$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();
