Using Nested Selects for Performance in Rails

It would be slow on MySQL, though.

If you EXPLAIN that query, it's a DEPENDENT SUBQUERY which has to fetch N*M records. It would be mitigated if the columns are property indexed, but two separate queries (N+M) just as eager loading would be faster and more predictable than nested scans in terms of IOPS.

"But there is no easy way to do this in ActiveRecord without sacrificing eager loading."

Define a relationship:

has_many :approved_comments, class_name: "Comments", condtions: {approved: true}

Then eager load approved_comments.

That works, but what if there are thousands of comments? Initializing N*M AR objects could be even slower than N+1 queries.

I wish if we could do something like:

has_one :approved_comments_count, -> { select(:post_id, 'COUNT(comments.post_id) as comment_count').group(:post_id) }, class_name: 'Comment'

and

posts.includes(:approved_comments_count).map(&:comment_count)

Author could use counter cache to solve the problem.

Author here. That would work for a count (so perhaps that was a poor example to choose for this post) but we also use calculated_attributes for many more complex cases (for example, querying the earliest date in 3 columns across 3 tables).

There is a gem called eager_group that you might find useful.