Avoiding sub-queries in named scopes

Question

I use named scopes all over the place.

When I have a named scope that deals with two different tables, I tend to query the second table in a sub-query. That way, if I mix and match with other tables I don't need to worry about reusing a table alias or having ambiguous column names that only appear in bizarre scenarios.

For example, suppose I have a posts table that is one-to-many related to a tags table. I might add a with_tags scope on my Post model like this:

named_scope :with_tags, lambda { |tags|
 tags = [tags] if tags.kind_of? String
 {:conditions => ["id IN (SELECT post_id FROM tags WHERE name IN (?))", tags]}
}

However, that doesn't seem ideal. Many databases can use a join more efficiently than they can use a sub-query. A query that looks like this might perform better:

SELECT DISTINCT posts.* FROM posts JOIN tags ON posts.id = tags.post_id WHERE tags.name IN (?)

How do other people do this? Do you use the :include/:join parameters cleverly to know what the aliases ActiveRecord will use?

Answer 1

You want to use what Rails calls eager loading. This is done with the :include parameter in your AR call. Add it to your lambda block.