

Using Nested Selects for Performance in Rails - kb21
http://blog.aha.io/index.php/using-nested-selects-for-performance-in-rails/

======
kenn
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.

------
yarb
"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.

~~~
kenn
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)

------
NARKOZ
Author could use counter cache to solve the problem.

~~~
schneidmaster
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).

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

