How to optimize this eloquent query? (check that a date range is not already taken)-CodePudding

Here is my Eloquent query, which allows to check if a date is already occupied or not, according to a "started_at" and an "ends_at".

$bookings = Booking::where(function ($q) use ($start, $end) {
            $q->orWhere('started_at', '>=', $start)
                ->where('started_at', '<=', $end)
                ->where('status', '!==', Booking::STATUS_CANCELED);
            })
            ->orWhere(function ($q) use ($start, $end) {
                $q->where('ends_at', '>=', $start)
                    ->where('ends_at', '<=', $end)
                    ->where('status', '!==', Booking::STATUS_CANCELED);
            })
            ->orWhere(function ($q) use ($start, $end) {
                $q->where('started_at', '<', $start)
                    ->where('ends_at', '>', $end)
                    ->where('status', '!==', Booking::STATUS_CANCELED);
            })->get();

The query works, but I think it's a bit long, it can probably be shortened.

Especially for the where that checks that the status is not "cancelled".

Thanks in advance

CodePudding user response：

your query could look like that :

       $bookings = Booking::where('status', '!==', Booking::STATUS_CANCELED)
        ->where(function ($q) use ($start, $end) {
            $q->where('started_at', '<=', $end)
                ->orWhere('started_at', '>=', $start);
        })
        ->orWhere(function ($q) use ($start, $end) {
            $q->where('ends_at', '>=', $start)
                ->where('ends_at', '<=', $end);
        })->orWhere(function ($q) use ($start, $end) {
            $q->where('started_at', '<', $start)
                ->where('ends_at', '>', $end);
        })->get();

CodePudding user response：

Haven't tested this out, but something like this should work:

$bookings = Booking::where('status', '!=', Booking::STATUS_CANCELED)
    ->where(function ($query) use ($start, $end) {
        $query->whereBetween('started_at', [$start, $end])
            ->orWhereBetween('ends_at', [$start, $end])
            ->orWhere(function ($query) use ($start, $end) {
                $query->where('started_at', '<', $start)
                    ->where('ends_at', '>', $end);
            });
    });