如何使用Laravel符号创建多个where子句查询?

I'm using the Laravel Eloquent query builder and I have a query where I want a WHERE clause on multiple conditions. It works, but it is not elegant.

我正在使用Laravel出色的查询构建器，我有一个查询，我想在多个条件下使用where子句。它能工作，但不优雅。

Example:

例子:

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

Is there a better way to do this, or should I stick with this method?

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

17 个解决方案

#1

In Laravel 5.3 you can use more granular wheres passed as array:

在Laravel 5.3中，可以使用更细粒度的位置作为数组传递:

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

Personally I haven't found use-case for this over just multiple where calls, but fact is you can use it.

就我个人而言，我还没有找到在多次调用中使用它的用例，但事实是您可以使用它。

Since June 2014 you can pass an array to where

从2014年6月开始，可以将数组传递到where

As long as you want all the wheres use and operator, you can group them this way:

只要你想要所有的where和操作符，你可以这样分组:

$matchThese = ['field' => 'value', 'another_field' => 'another_value', ...];

// if you need another group of wheres as an alternative:
$orThose = ['yet_another_field' => 'yet_another_value', ...];

Then:

然后:

$results = User::where($matchThese)->get();

// with another group
$results = User::where($matchThese)
    ->orWhere($orThose)
    ->get();

The above will result in such query:

上述情况将导致这样的查询:

SELECT * FROM users
  WHERE (field = value AND another_field = another_value AND ...)
  OR (yet_another_field = yet_another_value AND ...)

#2

Query scopes may help you to let your code more readable.

查询范围可以帮助您提高代码的可读性。

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

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

Updating this answer with some example:

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

In your model, create scopes methods like this:

在您的模型中，创建这样的范围方法:

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

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

Then, you can call this scopes while building your query:

然后，您可以在构建查询时调用此范围:

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

#3

You can use subqueries in anonymous function like this:

您可以在匿名函数中使用子查询，如下所示:

 $results = User::where('this', '=', 1)
            ->where('that', '=', 1)
            ->where(function($query) {
                /** @var $query Illuminate\Database\Query\Builder  */
                return $query->where('this_too', 'LIKE', '%fake%')
                    ->orWhere('that_too', '=', 1);
            })
            ->get();

#4

In this case you could use something like this:

在这种情况下，您可以使用如下内容:

            User::where('this', '=', 1)
                ->whereNotNull('created_at')
                ->whereNotNull('updated_at')
                ->where(function($query){
                    return $query
                              ->whereNull('alias')
                              ->orWhere('alias', '=', 'admin');
                });

It should supply you with a query like:

它应该向您提供如下查询:

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

#5

Conditions using Array:

条件使用数组:

$users = User::where([
       'column1' => value1,
       'column2' => value2,
       'column3' => value3
])->get();

Will produce query like bellow:

会产生如下问题:

SELECT * FROM TABLE WHERE column1=value1 and column2=value2 and column3=value3

Conditions using Antonymous Function:

条件使用反义的功能:

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

Will produce query like bellow:

会产生如下问题:

SELECT * FROM TABLE WHERE column1=value1 and (column2=value2 or column3=value3) and (column4=value4 and column5=value5)

#6

Multiple where clauses

多个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);
    }

finally getting the result

最后得到结果

    $result = $query->get();

#7

The whereColumn method can be passed an array of multiple conditions. These conditions will be joined using the and operator.

whereColumn方法可以传递一个包含多个条件的数组。这些条件将使用and操作符连接。

Example:

例子:

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

For more information check this section of the documentation https://laravel.com/docs/5.4/queries#where-clauses

要了解更多信息，请查看文档的这一部分:https://laravel.com/docs/5.4/queries#where子句

#8

Be sure to apply any other filters to sub queries, otherwise the or might gather all records.

确保对子查询应用任何其他过滤器，否则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();

#9

Without a real example, it is difficult to make a recommendation. However, I've never needed to use that many WHERE clauses in a query and it may indicate a problem with the structure of your data.

没有一个真实的例子，就很难做一个推荐。但是，我从来没有需要在查询中使用那么多WHERE子句，这可能表明数据结构有问题。

It may be helpful for you to learn about data normalization: http://en.wikipedia.org/wiki/Third_normal_form

了解数据规范化可能会有帮助:http://en.wikipedia.org/wiki/Third_normal_form

#10

You can use eloquent in Laravel 5.3

你可以在Laravel 5.3中使用“雄辩”

All results

所有的结果

UserModel::where('id_user', $id_user)
                ->where('estado', 1)
                ->get();

Partial results

部分结果

UserModel::where('id_user', $id_user)
                    ->where('estado', 1)
                    ->pluck('id_rol');

#11

    $projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
                                                ['status','<>','Pending'],
                                                ['status','<>','Not Available']])
                                    ->orwhere([['owner', 'like', '%'.$input.'%'],
                                                ['status','<>','Pending'],
                                                ['status','<>','Not Available']])->get();

#12

You can use array in where clause as shown in below.

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

$result=DB::table('users')->where(array(
'column1' => value1,
'column2' => value2,
'column3' => value3))
->get();

#13

use whereIn condition and pass the array

使用其中的条件并传递数组。

$array = [1008,1009,1010];

数组美元=(1008、1009、1010);

User::whereIn('users.id', $array)->get();

用户:在(“用户。id的数组)- >();

#14

Model::where('column_1','=','value_1')->where('column_2 ','=','value_2')->get();

OR

或

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

OR

或

Model::where(['column_1' => 'value_1','column_2' => 'value_2'])->get();

#15

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

#16

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

#17

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