[PHP知识库]laravel多条件查询方法(andor嵌套查询)

比如需要执行这样一条sql语句

select 
  * 
from 
  proofing_notice
where 
  (id between 1 and 10 or id between 50 and 70) 
  and complete = 1 
  and (title like 'a%' or title like 'b%');

解决方式:

$homeworks = Homework::where(function ($query) {
  $query->whereBetween('id', [1, 10])
     ->orWhereBetween('id', [50, 70]);
})->where('complete', 1)
->where(function ($query) {
  $query->where('title', 'like', 'a%')
     ->orWhere('title', 'like', 'b%');
})->get();

DB::table('users')
   ->where(function ($q) {  //闭包返回的条件会包含在括号中
       return $q->where('id', 3)
           ->orWhere([
               ['name', 'jay'],
               ['age', '>', '18']
           ]);
   })
   ->where('sex', 1)
   ->get();
 

sql语句:

select * from `proofing_notice` where `proofing_notice`.`color_id` in (12203, 12204) 
and `status` in (9) 
and `flag` = 0 
and `area_id` in (1, 2) 
and (`paper_type_id` = 1 or (`paper_type_id` = 0 and `delivery_date` is not null))
and `proofing_notice`.`deleted_at` is null 
order by `updated_at` desc

解决方式:

$grid->model()->with(['proofing_notice'
                    => function($q1) use($state,$area_id,$type) {
                        $q1->with(['sizegroup','notice_node_new'=>function($qd){
                            $qd->with(['deve_sales_name','deve_aid_name','deve_buyer_name']);
                        }]);
                        $q1->whereIn('status', $state);
                        $q1->where('flag',0);
                        $q1->whereIn('area_id', $area_id);
                        if ($type == 11){
                            $q1->where(function ($q2){
                                $q2->where('paper_type_id',1);
                                $q2->orWhere(function ($q3){
                                   $q3->where('paper_type_id',0);
                                    $q3->whereNotNull('delivery_date');
                                });
                            });
                        }
                    }
                ])->whereHas("proofing_notice",function($q1) use($state,$area_id,$type) {
                    $q1->whereIn('status', $state);
                    $q1->where('flag',0);
                    $q1->whereIn('area_id', $area_id);
                    if ($type == 11){
                        $q1->where(function ($q2){
                            $q2->where('paper_type_id',1);
                            $q2->orWhere(function ($q3){
                                $q3->where('paper_type_id',0);
                                $q3->whereNotNull('delivery_date');
                            });
                        });
                    }
                });