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.

The situation

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.

The problem

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:

DateRange.includes(:taken_dates)
  .where('date_ranges.start_date >= ? AND date_ranges.end_date <= ?',
          some_date, some_other_date)
  .where.not('(taken_dates.start_date >= :start_date AND taken_dates.start_date <= :end_date) OR (taken_date.end_date >= :start_date AND taken_date.end_date <= :end_date)',
          start_date: some_date, end_date: some_other_date)

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 DateRanges without TakenDates.

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.

The solution

DateRange.includes(:taken_dates)
  .where('date_ranges.start_date >= ? AND date_ranges.end_date <= ?',
          some_date, some_other_date)
  .where('taken_dates.id IS NULL OR NOT (taken_dates.start_date >= :start_date AND taken_dates.start_date <= :end_date) OR (taken_date.end_date >= :start_date AND taken_date.end_date <= :end_date)',
          start_date: some_date, end_date: some_other_date)

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.