CODEWITHSUNDEEP
rubyruby-on-rails-3
Victor
Victor

Reputation: 13368

Refactor this method in Rails to prevent SQL query from running twice

Using Ruby 1.9.2, Rails 3.1. I have the following code:

# review.rb
def calculate_rating(reviewable)
  total_rating = Review.select("rating").where(:reviewable_id => self.reviewable_id)
  sum_rating = total_rating.sum(:rating)
  new_rating_average = sum_rating.to_f / total_rating.size
  reviewable.update_attribute(:rating_average, new_rating_average)
end

total_rating is actually used to get the total counts of the number of ratings returned, whereas sum_rating is used to sum each total_rating. I notice that my query is run multiple times to achieve the result.

Is there any way to refactor this to increase the performance?

Thanks.

Upvotes: 1

Views: 376

Answers (2)

clyfe
clyfe

Reputation: 23770

Assuming reviewable has many reviews:

reviewable.update_attribute :rating_average, reviewable.reviews.average(:rating)

Or in one shot, if your DB supports the syntax [1]

Reviewable.connection.execute <<-eos
  UPDATE reviewable SET (rating_average) = ((
    SELECT avg(rating) FROM review WHERE reviewable_id = {reviewable_id}
  )) where id = {reviewable_id}
eos

[1] http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls875.htm

Upvotes: 1

Frederick Cheung
Frederick Cheung

Reputation: 84114

The active record api exposes the SQL average function so you should be able to do

average_rating = Review.where(:reviewable_id => self.reviewable_id).average(:rating)

More generally you may need to write custom sql, for example if the average function didn't exist, you could write

select count(*) as c, sum(rating) as total from ratings where ( ... )

if you want to count multiple things with differing sets of conditions you can use things like

select count(*) as number_of_ratings, SUM(IF(rating > 5, 1, 0)) as number_of_ratings_greater_than_5, SUM(rating) as total_score

to get, in 1 query the number of ratings, their sum and and the number of ratings > 5. Use this with caution though, you don't want to replace 2 fast queries with one very slow query!.

Upvotes: 1

Related Questions