0

I have a Schema like this:

User(:id)
Event(:id, :start_date, :end_date,:duration, :recurrence_pattern)
EventAssignment(:user_id, :event_id, :date)

recurrence_pattern is a rrule string (see https://icalendar.org/rrule-tool.html, https://github.com/square/ruby-rrule)

date is a date formatted like 'YYYY-MM-DD'

start_date and end_date are timestamps like 'YYYY-MM-DDTHH:MM:SS'

I want to find all users that have at least one allocation overlapping with 2 timestamps, say from and to

So I wrote a bit of postgres and arel

assignment_subquery = EventAssignment.joins(:event).where(
    '"user_id" = users.id AND
    (?) <= "event_assignments".date + (to_char("events".start_date, \'HH24:MI\'))::time + make_interval(mins => "events".duration) AND
    (event_assignments.date) + (to_char(events.start_date, \'HH24:MI\'))::time <= (?)',
from, to).arel.exists

User.where(assignment_subquery)

edit: some more postgres(@max)

assignment_subquery = EventAssignment.joins(:event).where(
'"user_id" = users.id AND
    ("event_assignments".date + (to_char("events".start_date, \'HH24:MI\'))::time + make_interval(mins => "events".duration),
    (event_assignments.date) + (to_char(events.start_date, \'HH24:MI\'))::time) 
 OVERLAPS ((?), (?))'
, from, to).arel.exists

Which works fine

my question is: Is there a better more rails way to do this?

Ismail Yilmaz
  • 1
  • 1
  • 1
    There is a more Postgres way to do it which is to use the OVERLAPS operator. https://andreigridnev.com/blog/2016-03-30-overlaps-operator-in-postgresql/ – max Nov 14 '22 at 18:27
  • If an event has a "start_date" and an "end_date" (both of which appear to be datetime). I would assume the "date" portion of this would be equal to the EventAssignment date. If that is the case then what need do you have for the duration? Seems like you could just use the start_date and end_date and one of the solutions provided [Here](https://stackoverflow.com/questions/74070757/how-to-validate-range-dates-with-rails-6/74071874) – engineersmnky Nov 14 '22 at 19:51
  • @engineersmnky no, that's not the case. An event may be, for example, recurring every day at the same time. Then the start_date of the event includes information about the first date of the series as well as the starting time. The end_date contains information about when the series should end(which is already included in recurrence_pattern, but this way queries are easier) Now, since an event can be at most frequent daily, an allocation can be identified by its date, the event and the user that is assigned to the event. A user may be assigned to the same event on multiple days. – Ismail Yilmaz Nov 14 '22 at 20:15
  • In that case what you have is probably the best you are going to get. "Is there a more rails way?" well I could certainly convert this to Arel but the only reason for that really would be portability and given the DB specific mechanisms here e.g. type casting and/or the use of OVERLAPS the SQL is not really portable. Only comment I have is that I have no idea how `"user_id" = users.id` is working since the User table is not joined here and even so I would recommend qualifying "user_id" e.g. `event_assignments.user_id` in case you join another table with a "user_id" column. – engineersmnky Nov 15 '22 at 20:12
  • For the subquery.arel.exists part, I got that from https://stackoverflow.com/questions/31754820/rails-activerecord-where-exists-query. There, the place isn't joined either. In any case, qualifying which user_id column to check is probably a good idea, thanks @engineersmnky! – Ismail Yilmaz Nov 16 '22 at 21:16
  • I apologize I overlooked this part `User.where(assignment_subquery)`. – engineersmnky Nov 16 '22 at 21:25

0 Answers0