Rails scope to order by counter

I am trying to show the "Top 10" for users with the most comments.

Comment has belongs_to :user, counter_cache: :comments_count.

User has:

def self.top_by_comment_count
  order('comments_count desc').limit(10).to_a
end

This works fine on my local machine, but on a Heroku deploy I get all the users with no comments at the top.

I thought this would be a relatively straightforward task, but I'm beating my head against it.

Thank you for any help!

CodePudding user response：

You probably have NULLs in the comments_count column.

The best thing would be to not have NULLs there at all, a little migration with this would fix that:

change_column_default :users, :comments_count, 0
change_column_null :users, :comments_count, false

Alternatively, you could tell PostgreSQL where you want the NULLs:

order('comments_count desc nulls last')

But really, just change the column's nullity and default to avoid the SQL NULLs altogether.

CodePudding user response：

Probably problem is that PostgreSQL orders NULL as not you expected

Users who have comments_count as nil are on the top of this list

How to fix it. You can run in rails console on Heroku

User.where(comments_count: nil).find_each { |user| User.reset_counters(user.id, :comments) }

This command will reset NULL to 0

Also I recommend to add new migration with default 0 of comments_count

Something like this

change_column :users, :comments_count, :integer, default: 0, null: false