LEFT OUTER joins in Rails 3
Solution 1:
@posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id").
joins(:blog).select
Solution 2:
You can do with this with includes
as documented in the Rails guide:
Post.includes(:comments).where(comments: {visible: true})
Results in:
SELECT "posts"."id" AS t0_r0, ...
"comments"."updated_at" AS t1_r5
FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
WHERE (comments.visible = 1)
Solution 3:
I'm a big fan of the squeel gem:
Post.joins{user.outer}.joins{blog}
It supports both inner
and outer
joins, as well as the ability to specify a class/type for polymorphic belongs_to relationships.
Solution 4:
Use eager_load
:
@posts = Post.eager_load(:user)
Solution 5:
By default when you pass ActiveRecord::Base#joins
a named association, it will perform an INNER JOIN. You'll have to pass a string representing your LEFT OUTER JOIN.
From the documentation:
:joins
- Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id
" (rarely needed), named associations in the same form used for the:include
option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations.If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table‘s columns. Pass
:readonly => false
to override.