Complex query filters with ActiveAdmin

I had expected this topic to be long, complicated and extremely difficult, but I was pleasantly surprised to learn how dead simple it was to accomplish using tools already built into ActiveAdmin, and more specifically, MetaSearch.

Here’s the scenario: My application contains a model which has attributes for both the start and end of a range. Specifically, a Production has both start_date and end_date columns which together define the span of time over which that production occurs. I wanted a way to search for productions in a time period, but I wanted to see any productions where the range of dates for the production fell within the query window I specified.

Let me give a better example. I have a production that begins on Dec. 1st and runs through Dec. 5th. That range, 12/1 – 12/5 is the production window. I want to query for any productions occurring between Dec. 2nd and Dec. 4th. So the query window is 12/2 – 12/4. The query window is more narrow than the production window, and so simple filtering on start_date or end_date won’t work.

Using simple queries like where start_date >= ? and end_date <= ?' doesn't work because it misses productions starting before but ending within my query window, and it similarly misses productions starting within but ending after my query window due to theANDqualifier. Changing theANDto anOR` wouldn’t work because it would essentially select every production ever (think about it).

After pondering it for a few minutes, I realized the answer was going to involve several steps:

  1. Find all Productions where start_date falls within the query window
  2. Find all Productions where end_date falls within the query window
  3. Find the union of results (1) and (2)

Writing this solution in pure Rails wouldn’t be too difficult, and it looks something like this:

>> set1 = Production.where 'start_date >= ? and start_date <= ?', Date.new(2011,12,02), Date.new(2011,12,04)
=> [a, b d] 

>> set2 = Production.where 'end_date >= ? and end_date <= ?', Date.new(2011,12,02), Date.new(2011,12,04)
=> [a, c, d, e]

>> result = set1 | set2
=> [a, b, c, d, e]

There are other ways to skin this cat, but I’m not going into details here because in a minute I’ll get to a much more elegant solution.

Integrating this into ActiveAdmin, though, I feared would be much tougher, since it has its own mechanism for building and processing filters.

Actually, it doesn’t.

ActiveAdmin relies on MetaSearch under the hood to build and process filters, and a short read of the documentation for MetaSearch revealed support for OR conditions, which is exactly what I needed.

Here’s how simple it was. In my Production admin configuration, I replaced this:

filter :start_date, :label => "Date Range", :as => :date_range

with this:

filter :start_date_or_end_date, :label => "Date Range", :as => :date_range

VoilĂ . That was it. MetaSearch handled the union condition perfectly, and ActiveAdmin didn’t even suspect a thing. Now my filter matches any production that begins within or ends within the specified query window.

Leave a Reply

Your email address will not be published. Required fields are marked *