.finding on a method in ActiveRecord

Question

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?

Answer 1

You can do

Article.sum(:total_cost, :conditions => ["total_cost > ?", 300])
Article.average(: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])

Answer 2

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 ...
end

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.

Answer 3

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..