Rails: Inner joins by association names

From http://habtm.com/articles/2006/05/10/inner-joins-by-association-names:

We can enjoy eager loading with association names.
[code]Member.find(:all, :include=>[:group, :profile])[/code]
But :joins option forces us to write raw SQL like this.
[code]Member.count(:joins=>"INNER JOIN groups ON groups.id = members.group_id ...")[/code]
Why should we do it by hand although those AR classes know their reflections? It’s too boring and painful, and far from DRY.
expand join query

So, why don’t you put this code to ‘vendor/plugins/expandjoinquery/init.rb’?
[code]class ActiveRecord::Base
class << self
private
def add_joins!(sql, options, scope = :auto)
scope = scope(:find) if :auto == scope
join = (scope && scope[:joins]) || options[:joins]
sql << " #{expand_join_query(join)} " if join
end

def expand_join_query(*joins)
joins.flatten.map{|join|
case join
when Symbol
ref = reflections[join] or
raise ActiveRecord::ActiveRecordError, "Could not find the source association :#{join} in model #{self}"
case ref.macro
when :belongs_to
"INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, primary_key, table_name, ref.primary_key_name]
else
"INNER JOIN %s ON %s.%s = %s.%s" % [ref.table_name, ref.table_name, ref.primary_key_name, table_name, primary_key]
end
else
join.to_s
end
}.join(" ")
end
end
end[/code]
Now, we can enjoy :joins option as same as :include one!
# simply inner join
[code]Member.find(:first, :joins=>:group)
=> SELECT * FROM members INNER JOIN groups ON groups.id = members.group_id LIMIT 1[/code]
# two inner joins
[code]Member.count(:joins=>[:group, :profile])
=> SELECT count(*) AS count_all FROM members
INNER JOIN groups ON groups.id = members.group_id
INNER JOIN profiles ON profiles.member_id = members.id [/code]

# symbol and raw SQL
[code]Member.find(:all, :joins=>[:group, "INNER JOIN addresses USING(address_id)"])
=> SELECT * FROM members
INNER JOIN groups ON groups.id = members.group_id
INNER JOIN addresses USING(address_id) [/code]

restrictions

This is just an idea and not deeply considered yet. So, there are following restrictions.

* cascading is not supported
* not work with :include option (i think)