Fetching users that have events in a certain time interval

[lucadegasperi]

Hello Jonathan,
Following your reply to the email I wrote you, Here's the problem I'm facing in more detail:

Given an events table that has begins_at, ends_at and user_id fields, the query I want to run should return the users (plus a relationship on the users table) that have at least an event in a given hour. The problem is that I need to run this query for every hour in a day. Totaling 24 queries.
What I came up with so far is the following query scope on the User model:

public function scopeWithEventsBetween($query, Date $beginning, Date $ending)
{
    return $query->whereHas('events', function ($query) use ($beginning, $ending) {
        $query->between($beginning, $ending);
    });
}

and this scope on the Event model:

public function scopeBetween($query, Date $beginning, Date $ending)
{
    return $query->where(function ($query) use ($beginning, $ending) {

        // ENDS IN THE INTERVAL
        $query->where($this->getTable() .'.ends_at', '>', $beginning)->where($this->getTable() . '.ends_at', '<=', $ending);

    })->orWhere(function ($query) use ($beginning, $ending) {

        // BEGINS IN THE INTERVAL
        $query->where($this->getTable() . '.begins_at', '>=', $beginning)->where($this->getTable() . '.begins_at', '<', $ending);

    })->orWhere(function ($query) use ($beginning, $ending) {

        // BEGINS AND ENDS OUTSIDE THE INTERVAL
        $query->where($this->getTable() . '.begins_at', '<', $beginning)->where($this->getTable() . '.ends_at', '>', $ending);

    });
}

The code to get the users with event in the next 24 hours is the following:

$situation = collect();
$beginning = Date::today();
$ending = Date::today()->addDay();

for ($day = $beginning->copy(); $day->lt($ending); $day->addHour()) {
    $usersForHour = User::with('skills')->withEventsBetween(
        $day->copy(),
        $day->copy()->addHour(),
    )->get();

    $situation->push($usersForHour);
}

I've tried to simplify the problem as much as possible but on my production database this fairly simple query makes the CPU jump to 100% (only caused by mysql) and the page averages 12 seconds to load (sometimes even 20s depending on the number of events). This makes it impossible to increase the time resolution to 30mins or 15mins as the number of queries would double or quadruple and the page load time would skyrocket.

So I wanted to know if you might help me figuring out a way to fetch the users in a more performant way. I'm fine even with just the user ids being fetched.

For the sake of being precise the actual query scope I'm running is the following:

public function scopeSituationBetween($query, Date $beginning, Date $ending)
{
    // has a shift but is not absent
    // present
    // substituting someone

    return $query->where(function ($query) use ($beginning, $ending) {
        $query->whereHas('shifts', function ($query) use ($beginning, $ending) {
            $query->between($beginning, $ending);
        })->whereDoesntHave('availabilities', function ($query) use ($beginning, $ending) {
            $query->between($beginning, $ending)->whereStatus('unavailable');
        });
    })->orWhere(function ($query) use ($beginning, $ending) {
        $query->whereHas('availabilities', function ($query) use ($beginning, $ending) {
            $query->between($beginning, $ending)->whereStatus('available');
        });
    })->orWhere(function ($query) use ($beginning, $ending) {
        $query->whereHas('shiftSubstitutions', function ($query) use ($beginning, $ending) {
            $query->between($beginning, $ending);
        });
    });
}

Thank you for your time.
Luca