laravel 子查询

public function test(Request $request){    

        $data=$request->all();;

        $where = [];  //用于外层查询条件

        $whereData = [];  //用于子查询条件

        if(!empty($data['device_group_id'])){  //管制区id

            $where['b.device_group_id'] = $whereData['device_group_id'] = $data['device_group_id'];

        }

        if(!empty($data['start_time'])){  //开始时间

            $whereData['start_time'] = $data['start_time'];

        }

        if(!empty($data['end_time'])){   //结束时间

            $whereData['end_time'] = $data['end_time'];

        }



        $data = DB::table('vip'. ' as a')

            ->rightJoin('gate_role as b','a.id','=','b.vip_id')   //有门禁权限的才记录

            ->where($where)

            ->whereNotIn('a.id',function ($query) use($whereData){

                $query->from('device_group as c')

                    ->leftJoin('device_device_group as d', 'c.id', '=', 'd.device_group_id')

                    ->leftJoin('device as e', 'd.device_id', '=', 'e.id')

                    ->leftJoin('visitor as f', 'e.mac', '=', 'f.mac')

                    ->where('c.id', $whereData['device_group_id'])

                    ->whereBetween('f.discern_time',[$whereData['start_time'],$whereData['end_time']])

                    ->select('f.vip_id');

            })

            ->orderBy('a.id', 'desc')

            ->get(array('a.id','a.name'))

            ->map(function($value){

                return (array)$value;

            })->toArray();

        dd($data);

}