CODEWITHSUNDEEP
ruby-on-railssortingactiverecord
masterial
masterial

Reputation: 2216

How do I sort records based on sum of a particular attribute in a set of associations in Ruby on Rails?

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!

Upvotes: 1

Views: 646

Answers (3)

MilesStanfield
MilesStanfield

Reputation: 4639

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')

Upvotes: 4

Damian Borowski
Damian Borowski

Reputation: 171

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.

Upvotes: 1

Rodrigo Machado
Rodrigo Machado

Reputation: 31

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!

Upvotes: 1

Related Questions