1

guys! I would like to sort comments based on the total ratings score where the total rating score is the sum of the ratings' score attributes for each comment.

class Rating < ActiveRecord::Base
  belongs_to :comment, :class_name => 'Comment', :foreign_key => 'comment_id'

end

class Comment < ActiveRecord::Base
  has_many :ratings
end

Rating schema
  create_table "ratings", force: true do |t|
    t.integer  "user_id"
    t.integer  "comment_id"
    t.integer  "score"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

Thanks for your help!

masterial
  • 2,166
  • 9
  • 33
  • 48

3 Answers3

4

You should be able to order by the sum of associated records columns like this

Comment.joins(:ratings).group('comments.id').order('sum(ratings.score) desc')
MilesStanfield
  • 4,571
  • 1
  • 21
  • 32
1

Take a look at this answer for doing the count through a select call. Order Players on the SUM of their association model. This would be the suggested way.

Another way would be to include a method to sum all the rating's scores in your comment model.

def rating_score_sum
  ratings.sum(:score)
end

Then, you can sort your collection using that method.

Comment.all.sort_by(&:rating_score_sum)

Though this will calculate the score sum of all ratings for each comment every time and may become an issue as your database grows. I would consider saving that sum on the comments table and updating it on every new rating.

Cheers!

Community
  • 1
  • 1
Rodrigo Machado
  • 31
  • 6
1

You should be able to do this easily with:

Comment.joins(:ratings).select('comments.*, SUM(ratings.score) as rating_score').order('rating_score DESC')

You could also try using includes instead of joins or even eager_load as it will preload the association (ratings) and improve the performance of this query.

Damian Borowski
  • 171
  • 1
  • 5