Rails - Dec 2007 - straight join in rails

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.





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---