Rails finds and sorts using data from the corresponding table

Question

I connected two models Businessesand Ratings. A business can be evaluated many times, and each rating stores an integer from 0 to 5.

I would like to create the Hall of Fame page, in which I will list the top 10 companies by sorting enterprises based on average ratings (which will use the model Ratings) and limiting the results to 10.

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

Answer 1

Assuming you have a has_many and belongs_to relationship between the two models, you can try using the (very convenient) 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 an average rating, you can include it in the: select parameter:

@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 "companies". and ratings. from the parameters: select and: order.

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

Answer 2

In your controller, you can capture the top 10 companies 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 }

:order => 'avg_ratings_rating' :order => 'avg(ratings.rating)'. . , , .

, - - :

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

:

top10 = Business.getTop10