Eloquent - a group of "from date" to "today" with an interval of one day

Question

I have a model Reservationthat has fields date_fromand date_to.

How can reservations be grouped into one time interval so that you can find out how many of them are active on a specific date?

Example

Reservation::groupBy('...')->get()

Conclusion:

reservations
==================
qty | date
------------------
3   | 2000-01-01
5   | 2000-01-02
2   | 2000-01-03
...

Answer 1

First add "date_from" and "date_to" to the $ date array on the model

protected $dates = ['date_from', 'date_to'];

Then

$min_date = Reservation::select(DB::raw('MIN(date_from) as min'))->first();
$max_date = Reservation::select(DB::raw('MIN(date_to) as max'))->first();
$data = [];

if (!is_null($min_date) && !is_null($max_date)) {
    $daterange = new DatePeriod(DateTime::createFromFormat('Y-m-d H:i:s', $min_date->min), new DateInterval('P1D') ,DateTime::createFromFormat('Y-m-d H:i:s', $max_date->max));

    $reservations = Reservation::all();
    foreach ($daterange as $date) {
        $data[$date->format('Y-m-d')] = $reservations->filter(function($reservation) use($date) {
            $carbon_date = Carbon::instance($date)->endOfDay();
            return ($reservation->date_from->startOfDay()->lte($carbon_date) && $reservation->date_to->endOfDay()->gte($carbon_date));
        })
        ->count();
    }
}