ActiveRecord's way of separating Ruby developers from writing SQL is fantastic, but sometimes its behavior can be a little surprising. I ran into some ActiveRecord-related trouble recently and wanted to share the solution.
I had a model, let's call it
DateRange that represents a range of dates (amazing, right?). Each date range could have many
TakenDates representing a range of dates that fall within its parent date range and are no longer available.
Let's say I'm trying to find out if a given range of dates is not taken within an existing
DateRange. That means that it falls within a given range and has not been taken by a
TakenDate. So I want to do a query that returns all
DateRanges and associated
TakenDates if they exist. Great! ActiveRecord has an
includes method just for this. So I wrote a query like so:
I know that is bewildering, but given two dates, it tries to find a
DateRange that it falls within that doesn't have a conflicting
TakenDate. It doesn't work, however. Why? Because it never returns any
DateRange without any
TakenDates. I was surprised about that, because I assumed the
where.not call would happily return any
For a while I was stymied at how I could do this with a single query. It would be possible to do two different queries and combine them, but that would be slower and would not leave me with an ActiveRecord collection.
That is one ugly query, but the key is specifying
taken_date.is IS NULL OR some condition. This returns everything without an association while still allowing you to do a query based on the association if it exists!
I don't doubt that there is a better way to tackle this problem (feel free to let me know!), but I think it is good to know some tricks for those corner cases where ActiveRecord doesn't make your life easy.