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...
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.
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')
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.