LEFT OUTER JOIN with ARel

To eager load records in Rails 3 use the includes method like so:

  Event.includes(:user)

This will load all the users who have events by using these queries:

  Event Load (2.3ms)  SELECT "events".* FROM "events" ORDER BY events.id DESC
  User Load (0.8ms)  SELECT "users".* FROM "users" WHERE ("users"."id" IN (1))

To then perform a LEFT OUTER JOIN on the users table and load everything in the one query, you must perform a query that accesses the users table, like this:

  Event.includes(:user).where(:users => { :username => "ryan" })

This will execute this query:

   SELECT "events"."id" AS t0_r0, [...] "users"."single_access_token" AS t1_r25 
   FROM "events" LEFT OUTER JOIN "users" ON "users"."id" = "events"."user_id" 
   WHERE ("users"."username" = 'ryan') 

So remember, kids - if you want to do an INNER JOIN, use joins; if you want to do a LEFT OUTER JOIN, use includes.

If you’re looking to do this in Rails 2.0 the code is this:

Event.all(:include => :user, :conditions => { :user => { :username => 'ryan' } } )

The query executed by this is pretty much identical to the Rails 3 version, but the syntax is just that tiny bit longer.

For more shiny stuff, take at a look at this blog post about the features in ARel 2.0.

Hat tips to Jason Weathered (@jasoncodes) for going through this with me and for Lucas Willet (@ltw_) for proof-reading this post.