我使用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很容易创建多个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();

其他回答

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->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)

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

 $results = User::where('this', '=', 1)
       ->where('that', '=', 1)
       ->where(
           function($query) {
             return $query
                    ->where('this_too', 'LIKE', '%fake%')
                    ->orWhere('that_too', '=', 1);
            })
            ->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')

代码示例。

首先:

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