Rails: Inner joins by association names

MattScilipoti (Matt Scilipoti) — Tue, 30 May 2006 08:58:02 GMT

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

We can enjoy eager loading with association names.



Member.find(:all, :include=>[:group, :profile])

But :joins option forces us to write raw SQL like this.



Member.count(:joins=>"INNER JOIN groups ON groups.id = members.group_id ...")

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’?



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

Now, we can enjoy :joins option as same as :include one!



# simply inner join

Member.find(:first, :joins=>:group)

=> SELECT * FROM members INNER JOIN groups ON groups.id = members.group_id LIMIT 1



# two inner joins

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 



# symbol and raw SQL

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)

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)

I hope someone will polish this up :)

DZone Snippets: enhance code

Rails: Inner joins by association names