code.isdangero.us

Making custom SQL play nice

June 24, 2009 - 2 Comments - ruby rails activerecord sql

So let’s say you have a custom SQL query which, for one reason or another, doesn’t fit nicely into ActiveRecord’s finder options. Here is an example using a subquery:

This query does just what the method describes. If you call Order.average_revenue_per_month, you will get back the average total revenue from orders each month. Great, that was easy!

What about associations and named scopes?

Hold up. ActiveRecord provides a great framework for mapping database tables to objects which can do amazing things as long as you stick to the basics. One of those things is the ability to call class methods on associations and named scopes. For example, we can call User.find(1).orders.average_revenue_per_month, and it should do exactly what you expect it to within the scope of that particular User’s Orders. But that only works if you’re staying inside the bounds of ActiveRecord’s finder methods.

In this case, it would be impossible to fit our query into the standard finder methods and still have it work with associations and named scopes as expected. So what do we do, throw our hands up in dismay and sooth our egos with a rereading of The Law of Leaky Abstractions? Nay! For there is a way.

It isn’t well-documented, but it is possible to get the current scope and use it more manually. This works for any kind of scope, including those brought on by association proxies or by named scopes. As long as your custom query respects this scope, it will play nice with all the ORM goodness that ActiveRecord so beautifully provides.

Specifically, we can call scope(:find) within a model’s class methods to get back a hash of normal Rails finder options for the current scope: :conditions, :limit, :order, etc. :conditions, which is the only option we really care about in this case, will already be formatted as a SQL WHERE fragment, sans the actual WHERE declaration.

Enough with the theory. Here’s how we can use it to make our custom SQL play nice:

As a wise man once said: Wa-da-ta. Your custom query will now work unfettered within the confines of even the most irrationally chained method calls. Just imagine:

Let the madness ensue.