Comparing DateTime with Laravel Query Builder

I was recently adding a feature to the CRM that I built and the feature would prevent the user from double booking a client. What I wanted was when a user would choose a start and end date it would check to see if there was already a booked event during that timeframe and if so it would display a message letting them know.

At first, I thought it would be pretty straightforward, but what I found out is it's actually a little more complex then I thought.

I originally thought I found a solution but the solution that I found wasn't good enough. It would throw a message if it was a double booking but it would also throw the message if it was on a different day but at the same time. That code looked like this:

/**
     * Handle the incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function __invoke(Request $request)
    {
        $session = $request->user()
            ->sessions()
            ->whereDate('session_start_date', Carbon::create($request->start, $request->user()->timezone)->toDateString())
            ->whereTime('session_start_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
            ->whereTime('session_start_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString())
            ->orWhere(function ($query) use ($request) {
                $query->whereTime('session_end_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
                ->whereTime('session_end_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString());
            })
            ->count();

        return response()->json(['exists' => $session ? true : false]);

    }

After some testing and figuring out this wasn't good enough I came up with the following code:

/**
     * Handle the incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function __invoke(Request $request)
    {
        $session = $request->user()
            ->sessions()
            ->whereDate('session_start_date', Carbon::create($request->start, $request->user()->timezone)->toDateString())
            ->whereTime('session_start_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
            ->whereTime('session_start_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString())
            ->orWhere(function ($query) use ($request) {
                $query->whereDate('session_end_date', Carbon::create($request->end, $request->user()->timezone)->toDateString())
                ->whereTime('session_end_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
                ->whereTime('session_end_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString());
            })
            ->count();

        return response()->json(['exists' => $session ? true : false]);

    }

Can you spot the difference? In the original piece of code I left out the $query->whereDate() in the orWhere() closure. We needed it so that it checks both the date and time instead of just the date. But this still isn't good enough. This will now throw a message anytime I select a start or end date. To finally fix it we needed to change the orWhere() to where() this way the SQL syntax uses "and" instead of "or". The final piece of code that worked was this:

/**
     * Handle the incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function __invoke(Request $request)
    {
        $session = $request->user()
            ->sessions()
            ->whereDate('session_start_date', Carbon::create($request->start, $request->user()->timezone)->toDateString())
            ->whereTime('session_start_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
            ->whereTime('session_start_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString())
            ->where(function ($query) use ($request) {
                $query->whereDate('session_end_date', Carbon::create($request->end, $request->user()->timezone)->toDateString())
                ->whereTime('session_end_date', '>=', Carbon::create($request->start, $request->user()->timezone)->toTimeString())
                ->whereTime('session_end_date', '<=', Carbon::create($request->end, $request->user()->timezone)->toTimeString());
            })
            ->count();

        return response()->json(['exists' => $session ? true : false]);

    }

When using Laravels query builder to compare dates it can be confusing. Especially when you're looking at the code and trying to break it down. But I ended up using trial and error, this didn't work so let's try this until it gave me the result I needed.

Do you know of a better way to accomplish the same result? If so please comment in the comments below so we can discuss it.

Blog

Comparing DateTime with Laravel Query Builder

Jonathon Ringeisen

Join Our Newsletter. No Spam, Only the good stuff.

Related