Rails find and sort, using data from a related table

Question

I have associated two models Businesses and Ratings. A business can be rated many times, and each rating stores an integer from 0 to 5.

I would like to create a "Hall of Fame" page, where I list the top 10 businesses, by sorting businesses based on average ratings (which would use the Ratings model) and limiting the results to 10.

I'm not sure how to write hall_of_fame method for the controller, or would I also need a helper for this?

Answer 1

Assuming that you have a has_many and belongs_to relationship between the two models, you can try using the (very handy) MySQL GROUP BY clause, which is supported in Rails:

@hall_of_fame = Business.find(
                               :all, 
                               :joins => :ratings, 
                               :group => 'business_id', 
                               :order => 'AVG(ratings.rating) DESC', 
                               :limit => 10
                             )

If you want to add the average rating, you can include it in a :select parameter, as such:

@hall_of_fame = Business.find(
                               :all,
                               :select => 'businesses.name, AVG(ratings.rating)'
                               :joins  => :ratings, 
                               :group  => 'business_id', 
                               :order  => 'AVG(ratings.rating) DESC', 
                               :limit  => 10
                             )

Naturally, if there are no conflicting column names between the tables, you can safely remove the leading "businesses." and "ratings." from both the :select and :order parameters.

You may want to create a method with this code in your model instead of having it in the controller, but that's up to you.

Answer 2

In your controller you can grab top 10 businesses with something similar to:

top10 = Business.average('ratings.rating', :joins => 'INNER JOIN ratings ON businesses.id = ratings.business_id', :group => 'businesses.id', :order => 'avg_ratings_rating DESC', :limit => 10 )

# example of using results of above line
top10.each { |id, avg| puts Business.find(id).name + " score: " + avg.to_s }

Instead of :order => 'avg_ratings_rating' you can write :order => 'avg(ratings.rating)'. As a result it will return an ordered hash. When you iterate over it you get what you wanted.

Probably a better way is to add a class method to your Business model like this:

def self.getTop10
  average('ratings.rating', :joins => 'INNER JOIN ratings ON businesses.id = ratings.business_id', :group => 'businesses.id', :order => 'avg_ratings_rating DESC', :limit => 10 )
end

Then in controller you can do:

top10 = Business.getTop10