CODEWITHSUNDEEP
ruby-on-rails
jjjjjjjj
jjjjjjjj

Reputation: 4483

rails query by count on has_many relationship

I have a model, for example, Post, and a Post has_many comments. I would like to query posts in order of posts with the most comments to the least. How can I do this with an active record query?

Right now I am trying:

Post.includes(:comments).order('COUNT(comments.post_id) DESC')

but I am getting the error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "comments")

Upvotes: 0

Views: 1333

Answers (1)

max
max

Reputation: 101811

Post.left_outer_joins(:comments)
    .group(:id) # required by Postgres
    .order(Arel.sql('COUNT(comments.*) DESC')) # https://github.com/rails/rails/issues/32995

If you want to use the count in the result you can select it as well:

Post.left_outer_joins(:comments)
    .select('posts.*, COUNT(comments.*) AS comment_count')
    .group(:id) # required by Postgres
    .order('comment_count DESC') # Use COUNT(comments.*) in Oracle

Another way to solve this is by using counter_cache.

Upvotes: 2

Related Questions