Matching rows by date on a Laravel 9 application using php-carbon objects

The background

I am building a Laravel application and I have an upsert method on a Booking Controller for updating/inserting bookings. On upsert.blade.php I want to display a <select> element with a list of days into which a booking can be moved (or inserted).

There is a 'holidays' table with only one column: 'day' (of type datetime, precision 6). Each entry on this table means the system will be on holidays for that day, so bookings cannot be made or transfered into days that appear on this table.

Now, I want the <option>s in the above mentioned <select> to be disabled when they correspond to a holiday.

What I tried:

The view (upsert.blade.php)

  <select>
    <option value="" disabled selected>Select</option>

    @foreach($days as $day)

    <option value="{{ $day['value'] }}" @disabled($day['disabled'])>
      {{ $day['display'] }}
    </option>

    @endforeach
  </select>

The controller action:

public function upsert()
    {
        $now = Carbon::now();
        $last = Carbon::now()->addDays(30);
        $holidays = DB::table('holidays');

        $days = [];

        // Populate $days with dates from $now until $last
        while($now->lte($last))
        {
            array_push($days, [
                'value' => $now->toDateString(),
                'display' => $now->format('l j F Y'),
                /* 
                 * Mark day as disabled if holidays matching current
                 * day is greater than 1 
                 * DOESN'T WORK
                 */
                'disabled' => $holidays->whereDate('day', $now)->count()
            ]);
            $now->addDay();
        }

        return view('upsert', [
            'days' => $days,
        ]);
    }

The problem

The line labelled 'DOESN'T WORK' doesn't work as expected (I expect the query to return 1 if there is a holiday for the current day in the loop, thus marking the day as disabled). It only matches the first day of the loop if it's a holliday, but it won't match any other days.

Note: I have cast the 'day' property of the Holiday model to 'datetime' so Laravel casts the value to a Carbon object when accessing it.

Attempts to solve it

I tried replacing

$holidays = DB::table('holidays');

with

$holidays = Holiday::all();

but that throws the following exception

Method Illuminate\Database\Eloquent\Collection::whereDate does not exist.

So I tried rewriting the query to (note whereDate was replaced by where):

'disabled' => $holidays->where('day', $now->toDateString().' 00:00:00.000000')->count()

But this would never match

The solution

After around 6 hours of fiddling about with this line, reading Laravel documentation and talking to ChatGPT, I couldn't come up with an answert to why this is happening so I replaced the problematic line with

'disabled' => Holiday::whereDate('day', $now)->count()

Which does the job but I think is terrible for performance due to so many (in my opinion unecessary) round trips to the database.

The question

Could anyone shed some light on this? Although I've found a solution, I don't think it would scale and I also didn't learn a thing from the experience, I still have no idea why the first query is only matching the first day and no other days. Or why the second one using where() doesn't match any days at all when it is comparing strings and I am using the exact format the strings are stored in on the database.

Or maybe the problem is not on the query, but on the Carbon object?

If you want to reproduce it, follow steps on this gist: https://gist.github.com/alvarezrrj/50cd3669914f52ce8a6188771fdeafcd

Solution

DB::table('holidays') instantiates an Illuminate\Database\Query\Builder object. The where method modifies that object in place.

So if you're looping from January 1st-3rd and are adding a new where condition on each loop, that's going to fail because now you are basically querying this. Obviously the day column cannot match 3 different dates.

SELECT * FROM holidays
WHERE DATE(day) = '2022-01-01'
  AND DATE(day) = '2022-01-02'
  AND DATE(day) = '2022-01-03'

That's also why it only worked on the first loop for you, because at that point there is only 1 where condition.

You would need to move the instantiation inside the while loop so that it gets reset on each loop. Which is basically what you did in your solution.

Re: performance, what you were trying to do would not have saved you any DB cycles anyway. Each time you call count() you are hitting the database, regardless of whether it's a new $holidays object or not.

If you're concerned about performance, one thing you could do is fetch all of the holidays between the start & end date in a single query.

// May need to call toDateString() on $now and $last
$holidays = Holiday::whereBetween('day', [$now, $last])
    ->get()
    ->pluck('id', 'day'); // Assuming day is a DATE column not DATETIME or TIMESTAMP

// This will give you a collection with an underlying array like this:
// ['2022-07-04' => 1, '2022-12-25' => 2]

while($now->lte($last))
{
    array_push($days, [
        // Now you can instantly look it up in the array by the date
        'disabled' => isset($holidays[$now->toDateString()]),
    ]);
    $now->addDay();
}