Hacker News Reader

Comment by fbonetti | Hacker News Reader

That's really easy.

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

View on HN

That is not the same query at all. You need to do a left join and a group by for it to become the same query. Wihout a left join posts without comments wont show up in the result.

You're totally right. My example is an inner join. I will admit that doing outer joins in ActiveRecord is quite painful. Here's a more realistic example, which is not nearly as pretty:

  Post.select("posts.*, COUNT(comments.*) AS comments_count").joins("LEFT JOIN comments ON comments.post_id = posts.id").group(:comments)

Thanks for the answer!

Unfortunately, what I needed to do back then were more complex than that (say, I need to perform query on top of subquery, e.g. "SELECT ... FROM (SELECT ...) AS a HAVING ... GROUP BY ..." sort of thing) which seems to be something ActiveRecord wasn't designed for.

In the end, I solve the problem by using Arel with an ActiveModel and unwrap all the data manually.

Wouldn't you need to do a group by in your query as well?

Yes

Reply on news.ycombinator.com