Laravel Eloquent date range between range

Question

I am building a website where i have a booking mechanism. The user can book a hotel room for X days if the room is available.

I am using:

• Laravel 5.4
• MySql

The room is unavailable if:

• It is already booked by another user
• The admin has set it as unavailable
• The room capacity is less or equal to the number of travellers

If have 3 tables to store those data:

• Rent: Contains the booking infos, such as rentStartDate and rentEndDate (as DateTime) and other fields (userId, rentalId, ...)
• Unavailabilities: When the admin set a room as unavailable, it's stored here. I have the fields unavailabilityStartDate, unavailabilityEndDate (as DateTime) and rentalId
• Rentals: This table contain all the infos regarding the room (capacity ,name, location, ...)

I am struggling to build a Eloquent query to check if the room is available before processing the user payment. Here is what i have for now:

public function isAvailableAtDates($rentalId, $startDate, $endDate, $capacity) {
    $from = min($startDate, $endDate);
    $till = max($startDate, $endDate);

    $result = DB::table('rentals')
        ->where([
            ['rentals.rentalId', '=', $rentalId],
            ['rentals.rentalCapacity', '>=', $capacity]
        ])
        ->whereNotIn('rentals.rentalId', function($query) use ($from, $till, $rentalId) {
            $query->select('unavailabilities.rentalId')
                ->from('unavailabilities')
                ->where([
                    ['unavailabilities.rentalId', '=', $rentalId],
                    ['unavailabilityStartDate', '>=', $from],
                    ['unavailabilityEndDate', '<=', $till],
                ]);

        })
        ->whereNotIn('rentals.rentalId', function($query) use ($from, $till, $rentalId) {
            $query->select('rent.rentalId')
                ->from('rent')
                ->where([
                    ['rent.rentalId', '=', $rentalId],
                    ['rentStartDate', '>=', $from],
                    ['rentEndDate', '<=', $till]
                ]);
        })
        ->select('rentals.rentalId')
        ->get();

    return count($result) == 1;
}

Let's say I have a row inside Unavailabilities with:

• unavailabilityStartDate = 2017-04-26 00:00:00
• unavailabilityEndDate = 2017-04-30 00:00:00

When calling the method with some dates outside of the range stored in Unavailabilities, i'm getting the expected result. When calling it with the exact same dates, i'm getting no result (which is what i want). So far so good! The problem is if i'm calling it with a start date between 26 of April and 30th and a end date later in May, i am still getting a result even tho i shouldn't.

Could anyone help me with that?

Answer 1

This is not a laravel, nor a mysql issue.

try this:

->whereNotIn('rentals.rentalId', function($query) use ($from, $till, $rentalId) {
        $query->select('unavailabilities.rentalId')
            ->from('unavailabilities')
            ->where([
                ['unavailabilities.rentalId', '=', $rentalId],
                ['unavailabilityStartDate', '<=', $till],
                ['unavailabilityEndDate', '>=', $from],
            ]);

    })
->whereNotIn('rentals.rentalId', function($query) use ($from, $till, $rentalId) {
        $query->select('rent.rentalId')
            ->from('rent')
            ->where([
                ['rent.rentalId', '=', $rentalId],
                ['rentStartDate', '<=', $till],
                ['rentEndDate', '>=', $from]
            ]);
    })

You need all rent and unavailabilities records that had been started before $till and hadn't been ended before $from date.

Try to draw a time diagram.