Comparing DateTime with Laravel Query Builder
Tags: laravel, php
Published: 2020-10-11 17:39:30

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.