Take the 2-minute tour ×

I'm using ActiveRecord and Ruby (outside of Rails) to track some statistics.

I added a method (total_cost) to one of my models to do calculations using a couple of the columns in the current model as well as a column from another model.

I'd really like to be able to use some of ActiveRecord's provisions for math (averaging, sums) and plain old finding using the method I defined, but any attempt to do so nets me (a perfectly understandable) 'Unknown column' error, e.g.

Article.find(:all, :conditions => ["total_cost > ?", 300])

As a result, I'm doing things in a manner we could describe as brute-forcey, just finding all the Articles then stuffing each one's total_value into an array and doing sums and averages with that.

Do I have any alternatives besides what I'm doing? Should I be looking past ActiveRecord to MySQL itself to calculate the values in question?

share|improve this question

3 Answers 3

You can do

Article.sum(:total_cost, :conditions => ["total_cost > ?", 300])

Given total_cost is a method:

total_cost = 'freelance_cost + effort * editor.hourly_rate'
Artice.sum(total_cost, :conditions => ["#{total_cost} > ?", 300], :joins => [:editor])
share|improve this answer
I can't do that. total_cost isn't a column in the Article table. It's a method I wrote that sums the freelance_cost and the product of effort and Article.editor.hourly_rate. When I try the syntax you suggested in irb, I get: ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'total_cost' in 'field list' –  mph Sep 9 '10 at 13:45

One option is to translate the way that total cost is calculated into an SQL expression so that you can then substitute that expression for any reference you might need to 'total_cost' in a query. That could potentially be quite cumbersome if total cost is complicated to calculate or depends on several tables.

If it is feasible it does sound like it makes sense to have a total_cost attribute on your Article model. Instead of having the calculation take place when someone calls the method a.total_cost you could calculate it when the model changes using a callback and save it then. For example:

def before_save
  self.total_cost = ... whatever ...

That would allow you to use the sum and average the total costs. It would be much more efficient than getting articles, calculating the total cost for each and then aggregating them. It would probably also be more efficient than calculating it each time it is requested.

That solution is dependent on the value for total cost not changing a lot. If it changes all the time based on various different objects changing then it will be more awkward to detect when total cost must change and less efficient to calculate it in this way. It would certainly make your calculations easier though.

share|improve this answer

find method creates SQL. You can't use total_cost method in it. So it needs to write the calculation of total_cost in SQL. If you want to use the method, you can filter necessary data from all.

@articles = Article.find :all
@articles = @articles.select {|a| a.total_cost > 300}

But this way spends more resources..

share|improve this answer

Your Answer


