TL;DR: I have a database with thousands of appointments that have start_time and end_time attributes. Given a date range (May 26-June 31), how do I find every appointment that happens throughout this range?
Appointment from May 15 to May 25 NOT included
Appointment from May 15 to May 29 needs to be included
Appointment from June 1 to June 3 needs to be included
Appointment from June 20 to July 5 needs to be included
Appointment from May 15 to July 15 needs to be included (most difficult part of the problem)
Appointment from July 1 to July 4 NOT included
We have an appointment model that has a start_time and an end_time. If an appointment occurs on any days during the monthly view, it needs to be loaded into the instance variable (@monthly_appointments) so that the simple_calendar gem can display it on the calendar.
Example: An appointment from June 1 to June 3 needs to show up when the user views the "June 2020" calendar. An appointment from May 15 to July 15 also needs to show up every single day during June.
There's 6-day padding on each side of the calendar dates, since if the week starts on a Saturday (June 1), you'll have May 26 - May 31 showing on the June calendar (see picture).
You'd think it was just as easy as saying "If an appointment starts or ends during the given month, add that appointment to the instance variable. However, there are cases when an appointment starts on May 15 and goes for 60 days, until July 15. The appointment neither starts nor ends during June, but it needs to still show up on the calendar.
Originally, we told users, "appointments cannot last longer than 6 months" and then we used this lookup where we assumed, "if the appointment started during the last 6 months, include it in the variable and then we'll let the calendar gem work out the rest."
@monthly_appointments = current_user.appointments.includes(:pet).where(start_time: (Time.zone.now).beginning_of_month - 6.months..(Time.zone.now).end_of_month + 6.days))
However, this query can sometimes save 3,000+ appointments in the variable, when, in reality, there are only 50-70 appointments that NEED to be shown for that month.
So, I wrote up the following code, and it succeeds in finding appointments that occur on at least one day during the calendar's timeframe. It compares the monthly calendar's range(May 26..June 31) and an appointment's range(June 1..June 3), and then looks for any dates that occur in both arrays (&). It works well, but it takes a bit of time because it needs to load ALL the appointments for a user (thousands) and then goes through each one to see if it occurs during that month.
Does anyone have any other clever solutions to this query issue? I'm sure something exists out there, but I haven't found it yet. Thanks!
month_dates = ((Time.zone.now.beginning_of_month - 6.days)..(Time.zone.now.end_of_month + 6.days)).to_a
@monthly_appointments = current_user.appointments.includes(:pet).select do |appt|
#create array of appointment dates and see if it intersects any of the monthly date array
appt_dates = (appt.start_time.to_date..appt.end_time.to_date).to_a
(month_dates & appt_dates).present?
end
Models:
Appointment(start_time, end_time, note, user_id, pet_id)
Pet
has_many appointments
User
has_many appointments
Here's an example.
carley = Pet.find(12)
Appointment.create(pet_id: carley.id, start_time: "May 15 2020 06:00:00", end_time: "July 15 2020 06:00:00"...)
When I'm looking at the June 2020 calendar, this appointment needs to show up on every single day.
