My Date With PostgreSQL Arrays
I've been working on an application to handle event data that depends on a fork of the Recurrence gem for building out recurrences.
That was working well until I needed to query and filter the data. I wanted an easy way to say:
Give me all events happening on any date from start_date through end_date with the tag awesome.
Since the events can be set to recur on specific days or periods between the start and end date, just asking for events that fall inside of the start_date and end_date parameters wouldn't work.
I was already using PostgreSQL's hstore to store the event window output from the Recurrence gem. The key value store contained all of the dates for the next 365 days. That would work right? Not really. Querying the hstore column to ask if it contained a value wasn't working out the way I wanted and didn't seem to be a performant option. It had to be easier.
I saw that the Array data type could do exactly what I wanted, quickly. The basic idea was to create an array of dates and ask the Event's Array column (called occurrence_dates) if it contained any of those dates. For this particular method, I wanted to know if any Event's occurrence_dates array overlapped the array I was passing in. You can use the && operator to determine if two arrays have elements in common.
where("occurrence_dates && ?", "{#{(start_date..end_date).to_a.join(',')}}")
While I still need to add support for open ended events (those with an undefined end date), this method should continue to work unmodified if I add that data to the occurrence_dates array. The best part about this is that I can still chain whatever other where clauses I need.
