Get most commented objects

Question

I'm using acts_as_commentable_with_threading gem to make users able to comment my blog posts.

What I want to do now is to display most commented posts but I have no idea how to query them (and as far as I know, the gem not provides such method). Can you write me some tips or ideas how to achieve something like that?

score 5 · Accepted Answer · answered Jul 12 '12 at 14:05

5

Here is a method that I use to return the top users that have posted the most items. It may help you with your issue. I put this in the Application Helper because it is part of my side navigation bar and will be used on every page in the web application.

def top_posters
    User.all(:select => "users.*, COUNT(user_id) as post_count",
       :joins => "LEFT JOIN posts AS posts ON posts.user_id = users.id",
       :group => "posts.user_id",
       :order => "post_count DESC",
       :limit => 5)
end

In my view, I have

<% top = top_posters() %>
<% for t in top %>
    <li><%= link_to t.username, user_path(t) %>
        (<%= t.posts.public_posts.count %>)</li>
<% end %>

answered Jul 12 '12 at 14:05

kobaltz

6,980
1
35
52

yeah, i think that's it :) I just can't find an error in my implementation: https://gist.github.com/3098896 If you have a time and desire to check it out, i would be extremely greatful (the problem is that this query returns completely bad `comments_count` number) – mbajur Jul 12 '12 at 15:36
I think that is because the commentable_id is part of a polymorphic design and you would need to return any targets that are article and where the ID matches. Check your database to see if there are many other things other than Article in a target or source field. So it would basically also match any other model that happens to have the same ID. – kobaltz Jul 12 '12 at 15:39
Oh right, i've completely forgot about that, now it's working :) Thank you VERY much // solution in question post – mbajur Jul 12 '12 at 15:57

score 2 · Answer 2 · edited May 12 '15 at 20:00

2

For Rails 4+

You should use something like this:

Article.select("articles.*, COUNT(commentable_id) as comments_count")
                    .joins("LEFT JOIN comments AS comments ON comments.commentable_id = articles.id")
                    .group("comments.commentable_id")
                    .order("comments_count DESC")
                    .where("commentable_type = 'Article'")
                    .limit(5)

edited May 12 '15 at 20:00

Makoto

104,088
27
192
230

answered Jan 18 '15 at 00:27

mercuryseries

41
2

1

And how does it differ from the solution posted in the original question ? – mbajur Jan 18 '15 at 06:59

Get most commented objects

2 Answers2