Maybe someone will find this useful:
I have a relatively complex (My)SQL query that results from having many
many :conditions and :includes, and MySQL seems to apply a wrong join
order so I wanted to force an order using the STRAIGHT_JOIN statement,
but rails doesn’t really let you do it. Having to construct this query
manually (there are 4 joins) and still have a right format for eager
association loading wasn’t really an option.
I digged into ActiveRecord and modifying the JoinDependency class to
output "STRAIGHT_JOIN"s instead of "LEFT OUTER JOIN"s looked like too
much work too, so I decided to take a shorter route: intercept the
generated sql query, and replace SELECT with SELECT STRAIGHT_JOIN.
Here’s a monkey-patch (written against ActiveRecord 1.5.14) that
achieves this behaviour:
module ActiveRecord
class Base
class << self
VALID_FIND_OPTIONS << :straight_join
alias_method :orig_add_joins!, :add_joins!
def add_joins!(sql, options, scope = :auto)
orig_add_joins!(sql,options,scope)
sql.gsub!(/^SELECT/,‘SELECT STRAIGHT_JOIN’) if
options[:straight_join]
end
end
end
end
So simply supply a :straight_join => true option to finder and it will
result in a SELECT STRAIGHT_JOIN … query.