如何创建多个Where子句查询使用Laravel雄辩?

我使用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();

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

965736 次浏览
小开

查询作用域可以帮助您提高代码的可读性。

http://laravel.com/docs/eloquent#query-scopes

用一些例子更新这个答案:

在你的模型中，像这样创建作用域方法:

public function scopeActive($query)
{
return $query->where('active', '=', 1);
}


public function scopeThat($query)
{
return $query->where('that', '=', 1);
}

然后，你可以在构建查询时调用这个作用域:

$users = User::active()->that()->get();
小开

你可以像这样在匿名函数中使用子查询:

 $results = User::where('this', '=', 1)
->where('that', '=', 1)
->where(
function($query) {
return $query
->where('this_too', 'LIKE', '%fake%')
->orWhere('that_too', '=', 1);
})
->get();
小开
最佳答案

在Laravel 5.3(从7.倍开始仍然成立)你可以使用更细粒度的数组:

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

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

自2014年6月起，您可以将一个数组传递给where

只要你想要所有的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', ...];

然后:

$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 ...)
小开 
$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);
}
小开

在这种情况下，你可以使用这样的代码:

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')
小开 
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'));
}
}
小开

一定要对子查询应用任何其他过滤器，否则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();
小开

# EYZ0

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

你可以在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');
小开

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

要了解更多信息，请查看文档的这一部分 # EYZ0 < / p >

小开

使用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)
小开 
$projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])
->orwhere([['owner', 'like', '%'.$input.'%'],
['status','<>','Pending'],
['status','<>','Not Available']])->get();
小开

您可以在where子句中使用数组，如下所示。

$result=DB::table('users')->where(array(
'column1' => value1,
'column2' => value2,
'column3' => value3))
->get();
小开 
Model::where('column_1','=','value_1')
->where('column_2 ','=','value_2')
->get();

或

// If you are looking for equal value then no need to add =
Model::where('column_1','value_1')
->where('column_2','value_2')
->get();

或

Model::where(['column_1' => 'value_1',
'column_2' => 'value_2'])->get();
小开 
DB::table('users')
->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})
->get();
小开

根据我的建议，如果你正在做筛选或搜索

那么你应该选择:

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

使用这个

$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
小开

使用纯Eloquent，像这样实现它。这段代码返回所有帐户处于活动状态的已登录用户。 # EYZ0 < / p >

小开

代码示例。

首先:

$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'));
小开

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

$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]
])
小开

使用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();
小开

我们使用这条指令根据用户类型分类和用户名两个条件来获取用户。

在这里，除了从profiles表中获取用户信息外，我们还使用两个条件在输入时进行过滤，以减少查询次数。

$users = $this->user->where([
['name','LIKE','%'.$request->name.'%'],
['trainers_id','=',$request->trainers_id]
])->with('profiles')->paginate(10);
小开

你可以在几种情况下使用，

$results = User::where([
['column_name1', '=', $value1],
['column_name2', '<', $value2],
['column_name3', '>', $value3]
])->get();

你也可以这样用，

$results = User::orderBy('id','DESC');
$results = $results->where('column1','=', $value1);
$results = $results->where('column2','<',  $value2);
$results = $results->where('column3','>',  $value3);
$results = $results->get();
小开

你可以这样做，这是最短的方法。

    $results = User::where(['this'=>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])->get();
小开

在Eloquent中，你可以这样做:

$results = User::where('this', '=', 1)
->orWhere('that', '=', 1)
->orWhere('this_too', '=', 1)
->orWhere('that_too', '=', 1)
->orWhere('this_as_well', '=', 1)
->orWhere('that_as_well', '=', 1)
->orWhere('this_one_too', '=', 1)
->orWhere('that_one_too', '=', 1)
->orWhere('this_one_as_well', '=', 1)
->orWhere('that_one_as_well', '=', 1)
->get();

提交答案