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 an…
