Untitled

use Illuminate\Support\Facades\DB;

$date1 = $params['date1'];
$date2 = $params['date2'];
$date3 = $params['date3'];
$status = $params['status'];

$subquery1 = DB::table('rooms as r')
    ->leftJoin('bookings as b', function($join) use ($date1, $status) {
        $join->on('r.id', '=', 'b.room_id')
             ->whereDate('b.date_from', '=', $date1)
             ->whereNotIn('b.status', $status);
    })
    ->select('r.id as room_id', 
             DB::raw('MIN(r.av_start_time) as start'), 
             DB::raw('IFNULL(TIME(MIN(b.date_from)), MAX(r.av_end_time)) as end'))
    ->groupBy('r.id')
    ->havingRaw("TIMEDIFF(end, start) >= '01:00:00'");

$subquery2 = DB::table('bookings as b1')
    ->leftJoin('bookings as b2', function($join) use ($date2, $status) {
        $join->on('b1.room_id', '=', 'b2.room_id')
             ->whereDate('b2.date_from', '=', DB::raw('DATE(b1.date_to)'))
             ->where('b2.date_from', '>=', DB::raw('b1.date_to'))
             ->whereNotIn('b2.status', $status);
    })
    ->select('b1.room_id', 
             DB::raw('TIME(b1.date_to) as start'), 
             DB::raw('TIME(MIN(b2.date_from)) as end'))
    ->whereDate('b1.date_from', '=', $date2)
    ->whereNotIn('b1.status', $status)
    ->groupBy('b1.room_id', 'b1.date_to')
    ->havingRaw("start IS NOT NULL AND end IS NOT NULL AND TIMEDIFF(end, start) >= '01:00:00'");

$subquery3 = DB::table('rooms as r')
    ->leftJoin('bookings as b', function($join) use ($date3, $status) {
        $join->on('r.id', '=', 'b.room_id')
             ->whereDate('b.date_from', '=', $date3)
             ->whereNotIn('b.status', $status);
    })
    ->select('r.id as room_id', 
             DB::raw('IF(COUNT(b.date_from) = 0, r.av_end_time, TIME(MAX(b.date_to))) as start'), 
             'r.av_end_time as end')
    ->groupBy('r.id')
    ->havingRaw("TIMEDIFF(end, start) >= '01:00:00'");

$results = $subquery1
    ->union($subquery2)
    ->union($subquery3)
    ->get();

return $results;