Laravel Eloquent get where doesnt have, search where has when not null

Question

This question is really hard to word, so I apologise for the title!

Lets say I have a Rooms model and a Bookings model, 1 room can have many bookings.

I am building a table with filters, and I need to be able to run a filter on the booking relationship IF there is a booking, else grab the record anyway.

Lets say my room has a booking relationship like so

public function latestBooking(){
    return $this->hasOne('App\Models\Bookings', 'room_id', 'id')
    ->orderBy('expiry_date', 'DESC')->limit(1);
}

The above relationship gets me the latest booking.

I'm running a 'vacant from' filter like so

if ($request->has('filters.vacant_from')) {
    $rooms = $rooms->whereHas('latestBooking', function ($query) use ($request) {
        $query->where('expiry_date', '<', Carbon::createFromFormat('d/m/Y',$request->input('filters.vacant_from'))->format('Y-m-d'));
    });
}

The issue is that this only gets rooms that have a booking and are available after the date specified. I need it to be able to search all rooms, if it does have a booking, check if the expiry date is after the date specified, if it doesn't have a latest booking, get it anyway as its a vacant room.

Basically how would I structure my filter function so that it runs like this

Search all rooms, if it has a latest booking, check its expiry date is after the date specified and only grab it if its true/vacant, if it doesn't have a latest booking then grab it anyway as it is vacant

Answer 1

I have a suggestion for you regarding your latestBookingRelationship. You should create another relationship and add a greater than today condition to it and maybe name it activeLatestBooking.

Then, your latest Booking should comprise all booking rooms irrespective of whether their booking is active or not.

public function activeLatestBooking(){
        return $this->hasOne('App\Models\Bookings', 'room_id', 'id')
        ->where('expiry_date', '>', date('d/m/Y', strtotime('today')))->orderBy('expiry_date', 'DESC')->limit(1);
    }

public function latestBooking(){
    return $this->hasOne('App\Models\Bookings', 'room_id', 'id')
    ->orderBy('expiry_date', 'DESC')->limit(1);
}

Then, to answer your question:

if ($request->has('filters.vacant_from')) {
        $rooms = $rooms->whereHas('latestBooking', function ($query) use ($request) {
            $query->where('expiry_date', '<', Carbon::createFromFormat('d/m/Y',$request->input('filters.vacant_from'))->format('Y-m-d'));
        })->orWhereDoesntHave('latestBooking');
    }

What the filter query does is this: It gets all latest bookings whose expiry_date is less than the vacant_from date...and the orWhereDoesntHave gets all rooms that have never been booked.