Dynamic ActiveRecord querying in Rails 4 by example

One of my biggest frustrations with ActiveRecord and many other ORMs (looking at you, node-orm2) is the lack of a solid query builder to join various, dynamic query filters into a single  SQL statement.  It seems obvious that this should be the meat-and-potatoes of the entire query language abstraction framework, and yet there is still no clean way to build a SQL statement containing a variable combination of attribute filters.  Below we’ll examine some different functional (yet still ugly) techniques for doing so.

Imagine for a second you are building an app to best match prospective guitar players with the guitar of their dreams.  To do so, you build a slick web interface with a large number of filter inputs for future rockers to drill-down based on what they deem important, and want to return a ranked list of guitars matching this criteria.  You begin by defining a basic schema (oversimplified for our purposes) and writing the migrations.

 

 

After gathering and inserting a substantial amount of guitar inventory & spec data from Guitar Center and local pawn shops, you begin drafting the API.  Rock enthusiasts should be able to filter on all guitar model attributes, though they may only choose to filter on one or two.  Seems easy, right?  This brings us to Ugly Solution #1:

ActiveRecord evaluates each lazily, so the query will not execute until it is fully built and returned.  That means @guitars will contain the proper runtime clause, though it is rather annoying to have to write the same overriding logic to sub-select and drill into the query.  And the lazy evaluation isn’t all that intuitive.  So let’s try writing a key-value abstraction function to join parameters with their filter attribute.

 

Sure does look prettier, and way less code.  But what about the price and merchant city filter?  These cannot be achieved with the simple hash matching above.  Well crap.  Let’s try that again, this time building the SQL query a bit more manually.

 

The DRY in you hates all of the copy-paste.  It gets the job done, but what if we want to add new filterable attributes down the road (things like neck shape, fret count, pickup style, etc)?  Using the above convention thats +5 lines of code for every filterable attribute!  And it sits in the API controller layer which is supposed to be thin!  OK, fine.  Let’s sober up and try that again.  Time to consult the rails guides and refactor this using model scopes, their recommended way of handling this kind of dynamic chaining.

 

Or using class methods:

Really, Rails?  We can’t group filters inside a scope without knowing beforehand which filters a rocker will select.  As these filters are each independent of each other (except for maybe ‘make’ and ‘model’), we need to create a separate model scope for each.  So for each new filterable attribute, we need to switch between model and controller to create the scope and call it.  This is not only annoying, it is hardly maintainable.  Yet this is the recommended way of chaining WHERE clauses – a very common use case.  Am I missing something?  Hopefully.  Comments appreciated.