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

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


当前回答

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

其他回答

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

$result=DB::table('users')->where(array(
'column1' => value1,
'column2' => value2,
'column3' => value3))
->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();

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