Space Vatican

Ramblings of a curious coder

The Difference Between :include and :joins

I think some people occasionally mix up :include and :joins (or possibly don’t know of the existance of :joins).

:include is for loading associations. Before Rails 2.1 it will always left outer join the appropriate tables, starting with rails 2.1 it will either load them with a separate query or it will join the appropriate tables.

:joins is for joining (duh). It either takes an sql fragment (eg “INNER JOIN foos on foos.id = foo_id”) or association names in a variety of ways and joins the relevant tables. For example:

1
2
3
4
5
:joins => :products
:joins => [:products, :customers]
:joins => [:products, {:customers => [:friends, :foes]}]
:joins => [:products, {:customers => {:friends => :parents}}]
:joins => {:order => {:customer => {:address => :some_other_association}}}

and so on. You can nest these as deeply as you want (although bear in mind that if you end up requiring a 23 way join you may want to rethink your strategy). The same notation for nested associations is used by :include. The one difference is that :joins creates inner joins for you (if you desperately need those outer joins, you can always use the string form of :joins, but you will have to write the sql fragment explicitly).

If what you want is to use attributes from the joined tables for sorting or in your conditions then both :include and :joins will work since they both cause the relevant tables to be joined. But :include then does a lot more work massaging the results that come back from the database, instantiating lots of activerecord objects, gluing together all the relationships in the appropriate manner. If all you wanted was to order or filter results based on some of the joined attributes then this work is wasted.

Just to show that I’m not making things up, I performed the following unscientific test:

1
2
3
4
5
6
7
8
9
10
Benchmark.bm(7) do |x|
  x.report("include:") do
    Customer.find :all, :include => :customer_detail,
                 :conditions => "customer_details.date_of_birth >= '1980-01-1'"
  end
  x.report("joins:") do
    Customer.find :all, :joins => :customer_detail,
                 :conditions => "customer_details.date_of_birth >= '1980-01-1'"
  end
end

Some customers have provided extra details which we store in a separate table, and we want to get all customers born after 1980.

The results:

usersystemtotalreal
include1.3100000.0400001.350000( 1.532910)
joins0.3500000.0400000.390000( 0.454001)

In short, don’t use :include unless you will actually be accessing those associations and want to avoid the hit caused by loading them from the database one by one.