I want to get the list of users who were present in the club from 10 AM to 12 PM using an SQL query in Laravel eloquent

thank you for reading this issue. please help me with the below issue

I want to user list which users in the club at the time between

022-11-07 11:32:48 - 2022-11-07 12:32:48

below is data store in DB

In Time--------------------Out Time----------------- be show in result
2022-11-07 11:32:48 - 2022-11-07 12:32:48 => 1
2022-11-07 10:32:48 - 2022-11-07 11:33:00 => 1
2022-11-07 12:32:00 - 2022-11-07 13:32:00 => 1
2022-11-07 11:45:00 - 2022-11-07 12:15:00 => 1
2022-11-07 10:00:00 - 2022-11-07 13:00:00 => 1
2022-11-07 09:30:00 - 2022-11-07 11:30:00 => 0
2022-11-07 12:35:00 - 2022-11-07 13:32:48 => 0

$activity = Activity::findOrFail($activity_id);

$users = User::whereHas('activities', function ($q) use ($activity) {
   $q->where('activities.activity_id', $activity->activity_id);
})->whereHas('bookingActivities', function ($query) use ($booking) {
   $query->whereNull('cancelled_by')
   ->whereBetween('bookingActivities.entry_time', [$booking->entry_time, $booking->exit_time])
   ->orWhereBetween('bookingActivities.exit_time', [$booking->_entry_time, $booking->exit_time]);
})->paginate();

I have add the query and it will give me 4 result it Should be 5

I am working on the lumen framework. i am use eloquent for get data I want to Logic or query to get all the user which in club in 10 to 11 but in my query 2022-11-07 10:00:00 - 2022-11-07 13:00:00 => 1 record not get. i try to get user using between the entry time and exit time but 2022-11-07 10:00:00 - 2022-11-07 13:00:00 => 1 record not get i in query but it should in.

please help me to how can i add the query so i can get expect result.

thanks in advance

Solution

Your query works by the logic that

either the user logged in between your Start ($booking->entry_time) and End ($booking->exit_time)

->whereBetween('bookingActivities.entry_time', [$booking->entry_time, $booking->exit_time])

or logged out between the Start and End of your search timeframe.

->orWhereBetween('bookingActivities.exit_time', [$booking->entry_time, $booking->exit_time])

You are missing one value because in your example a User entered before your Start and left after the End of your searched timeframe. Neither of your time conditions fits that case.

To solve it you can put the user's entry_time and exit_time into perspective to the Start value of your searched timeframe.

By adding for example the following condition, you search for all users who logged in before and exit after the Start value of your searched timeframe.

->orWhere(function ($query) {
       $query->where('bookingActivities.entry_time', '<', $booking->entry_time)
       ->where('bookingActivities.exit_time', '>', $booking->entry_time);
            })