CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Bach Dao
Bach Dao

Reputation: 125

Rails active record order by sum of a column

So I have 2 Models Posts and Topics

Here I want to get the most view topics and order it DESC (the highest total views of all posts tagged with that topic) using rails active record. Here is my current code of what I am trying to do but it is not correct :-

class Topic < ApplicationRecord
  has_many :posts
  scope :ordered, -> {
    joins(:posts).order("sum(posts.viewed) DESC").limit(2).uniq
  }
end

Upvotes: 1

Views: 182

Answers (2)

FAFA REN
FAFA REN

Reputation: 58

You need to group your topics_id

class Topic < ApplicationRecord
 has_many :posts
 scope :ordered, -> {
 joins(:posts).group("topics.id").order('SUM(posts.viewed) desc').limit(2).uniq
 }
end

This would work

Upvotes: 2

Yshmarov
Yshmarov

Reputation: 3749

It is a bad pattern to sum childer and than order by the sum.

I would advise you to add a total_views:integer column to your Topic.rb and update it whenever the sum of post.views changes.

When a child post's viewed value increases, you can call a callback to automatically update the total_views column.

Post.rb can have something like:

after_create do
  topic.update_total_views
end
after_update do
  topic.update_total_views
end
after_destroy do
  topic.update_total_views
end

Topic.rb:

def update_total_views
  update_column :total_views, (posts.map(&:viewed).sum)
end

Than in your controller you can call Topic.all.order(total_views: :desc)

Upvotes: 0

Related Questions