laravel多条件查询方法(and,or嵌套查询)

比如需要执行这样一条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');
                            });
                        });
                    }
                });

sql 语句：

select * from `proofing_notice` where `flag` = 0  and `properties_id` in (2) and exists (select * from deve_style where `proofing_notice`.`exploit_id` = `deve_style`.`id`) and ((`nuclear_date` between '2022-04-01 00:00:00' and '2022-05-01 00:00:00' and `paper_type_id` in (0, 2)) or (`transport_date` between '2022-04-01 00:00:00' and '2022-05-01 00:00:00' and `paper_type_id` in (0, 2))) and `proofing_notice`.`deleted_at` is null

解决方式:

$model = ProofingNotice::with(['exploit' => function($q){
            $q->with(['client','devedep','deveaids','client_branch','brand']) ;
        }, 'colors'=>function($q){
            $q->with(['place']) ;
        }]);
        $model = $model->where('flag',0);
        $model = $model
            ->whereIn('status',$arr_status)
            ->whereIn('properties_id',$arr_properties);
        if ($type_id == 2){
            //打样组
            $model = $model->with(['sample_num'=>function($qn){
                $qn->where('flag',4);
            }]) ->whereHas('sample_num', function ($qn) {
                $qn->where('flag',4);
            });
        }
        else{
            $model = $model->with(['notice_size']);
        }
        $model->whereHas('exploit',function ($q) use ($client_id,$depid,$brand_id,$deve_aid){
            $q->from(DB::raw( "deve_style"));
            if(isset($client_id)){
                $q->where('client_id',$client_id);
            }
            if(isset($depid)){
                $q->whereIn('deve_dep',$depid);
            }
            if(isset($brand_id)){
                $q->where('brand_id',$brand_id);
            }
            if(isset($deve_aid)){
                $q->where('deve_aid',$deve_aid);
            }
        });
        if(isset($place_id)){
            $model->whereHas('colors',function ($q) use ($place_id){
                $q->where('place_id',$place_id);
            });
        }

        if($type_id == 2) {
            $model = $model->whereBetween('quality_date', [$start_date, $end_date]);
        }
        else{
            $model = $model->where(function ($qw) use($start_date,$end_date){
                $qw->where(function ($qd) use($start_date,$end_date){
                    $qd->whereBetween('nuclear_date', [$start_date, $end_date]);
                    $qd->whereIn('paper_type_id', [0,2]);
                });
                $qw->orWhere(function ($qd) use($start_date,$end_date){
                    $qd->whereBetween('transport_date', [$start_date, $end_date]);
                    $qd->whereIn('paper_type_id', [1,3]);
                });
            });
        }

        if (!Admin::user()->isAdministrator()){
            $model = $model->whereIn('area_id',$place_from);
        };
        $data = $model->get()->toArray();

    $map = [
        ['place_work','like','%采购%'],
        ["status","=","1"]
    ];
    
    $deveSale = DingEmployee::getSelectApiOptions($map);

    public static function getSelectApiOptions($where = array())
    {
        $options = (new static())->where($where)->select('userid','name')->get();
        $selectOption = [];
        foreach ($options as $option){
            $selectOption[] = [
                'value' => $option->userid,
                'label' => $option->name,
            ];
        }
        return $selectOption;
    }