如何使用Laravel Eloquent创建多个Where子句查询？

18 回答

• 68

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

  回复于 2024-04-29T10:48:20+08:00
• 4

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

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

  回复于 2024-04-29T10:48:20+08:00
• 50

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

  回复于 2024-04-29T10:48:20+08:00
• -15

  没有一个真实的例子，很难提出建议 . 但是，我从来不需要在查询中使用那么多WHERE子句，这可能表明数据结构存在问题 .

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

  回复于 2024-04-29T10:48:20+08:00
• 14

  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)
  

  回复于 2024-04-29T10:48:20+08:00
• 8

  根据我的建议，如果你正在进行过滤或搜索

  那你应该去：

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

  回复于 2024-04-29T10:48:20+08:00
• 3

  在这种情况下，你可以使用这样的东西：

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

  回复于 2024-04-29T10:48:20+08:00
• -3

  您可以在where子句中使用array，如下所示 .

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

  回复于 2024-04-29T10:48:20+08:00
• 1

  使用 whereIn 条件并传递数组

  $array = [1008,1009,1010];

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

  回复于 2024-04-29T10:48:20+08:00
• 8

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

  回复于 2024-04-29T10:48:20+08:00
• 24

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

  有关更多信息，请查看文档的这一部分https://laravel.com/docs/5.4/queries#where-clauses

  回复于 2024-04-29T10:48:20+08:00
• 4

  Multiple where clauses

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

  回复于 2024-04-29T10:48:20+08:00
• 2

  您可以在 Laravel 5.3 中使用雄辩

  所有结果

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

  部分结果

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

  回复于 2024-04-29T10:48:20+08:00
• 4

  In Laravel 5.3 你可以使用更多颗粒状的wheres作为数组传递：

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

  就个人而言，我没有在多个 where 调用中找到用例，但事实是你可以使用它 .

  Since June 2014 you can pass an array to 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 ...)
  

  回复于 2024-04-29T10:48:20+08:00
• 3

  查询范围可以帮助您使代码更具可读性 .

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

  回复于 2024-04-29T10:48:20+08:00
• 392

  请务必将任何其他过滤器应用于子查询，否则可能会收集所有记录 .

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

  回复于 2024-04-29T10:48:20+08:00
• 0

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

  回复于 2024-04-29T10:48:20+08:00
• 0

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

  回复于 2024-04-29T10:48:20+08:00