How can I add a virtual "count" column to a select query in rails?

Go To StackoverFlow.com

1

I have a post model, and post has_many :comments, :as => :commentable (polymorphic). I am looking for a way that I can fetch all posts, and have a virtual attribute on the record which will display how many comments belong to that post.

I was thinking that I could just do:

Post.select("posts.*, count(comments.id) as post_comments").joins(:comments)

However, that returns only one record, with post_comments set to ALL comments in the entire database, not just those belonging to the record...

2012-04-03 19:48
by patrick


3

I think the problem is that your count(comments.id) just does one count for the entire joined table. You can get around this with a nested query:

Post.select("posts.*, (SELECT count(comments.id) FROM comments WHERE comments.post_id=posts.id) AS post_comments")

You don't need the join in this case, since the comments table is not used in the outer query.

2012-04-03 21:17
by tsherif
this is perfec - nathanengineer 2015-02-13 07:46


5

Actually, what you are missing is a group clause. You need to group by site, otherwise the count() aggregation collapses everything to one record as you see.

Try this:

Post.select("posts.*, count(comments.id) as post_comments")
    .joins(:comments)
    .group('posts.id')
2012-08-16 11:46
by Luke Ehresman


-1

I would do that with variables in Post model. 1st I would try to find the post I am looking for somehow (you can find it by wichever parameter you want,below I show the example with searching the id param).

@post = Post.find(params[:id])

When you find the post you were looking for, finding out comment number is preety easy, try something along the lines of...

@comments = @post.comments.size

...wich would return an integer.

2012-04-03 19:58
by oFca