When construct_finder_sql_for_association_limiting is generating its optimized query to find ids it only joins the minimum number of tables. It uses all tables required from options[:conditions] and options[:order] and discards the rest. This works most of the time but throws a big error when the conditions aren't in a table immediately adjacent to the model.
# this works fine (and the optimizing query leaves off the `comments` table):
Author.find(:all, :include=>{:posts=>:comments}, :conditions => "posts.type ='SpecialPost'", :limit => 10)
# optimizing SQL generated:
"SELECT DISTINCT `authors`.id FROM `authors` LEFT OUTER JOIN `posts` ON posts.author_id = authors.id WHERE (posts.type ='SpecialPost') LIMIT 10"
# But this breaks:
Author.find(:all, :include=>{:posts=>:comments}, :conditions => "comments.type = 'Comment'", :limit => 10)
"ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'posts.id' in 'on clause': SELECT DISTINCT `authors`.id FROM `authors` LEFT OUTER JOIN `comments` ON comments.post_id = posts.id WHERE (comments.type = 'Comment') LIMIT 10"
The easy solution would be to not throw away any tables in the optimizing query. A more difficult solution might be to include only the tables between the model and the tables used in :conditions and :order.
Easy solution attached.
