laravel 临时表查询

搜索空宿舍、空床

宿舍表 id ,code, bed_number

床位表 id, room_id, is_empty

$query1 = DB::table('dorm_bed')->select('room_id',DB::raw('convert(SUM(is_empty),SIGNED) as empty_number'))->groupBy('room_id');

$query = $roomModel->from('dorm_room as dr')
    ->select('dr.*', 'dd.name','bed.empty_number')
    ->leftJoin('dorm_building as dd','dr.building_id','=','dd.id')
     ->leftJoinSub($query1,'bed',function(Builder $query2){
         $query2->on('dr.id','=','bed.room_id');
     })
     ->where('dr.is_delete', '=', 0);

if(!empty($building_id)){
    $query = $query->where('dr.building_id','=',$building_id);
}
if($type == 1){ //空宿舍
    $query = $query->whereRaw('dr.bed_number = empty_number');
}
if($type == 2){ //空床位
    $query = $query->whereRaw('empty_number > 0');
}

$list = $query->limit(get_limit())
            ->offset(get_offset())
            ->orderBy('id')
            ->get();