LEFT OUTER joins in Rails 3

Question

I have the following code:

@posts = Post.joins(:user).joins(:blog).select

which is meant to find all posts and return them and the associated users and blogs. However, users are optional which means that the INNER JOIN that :joins generates is not returning lots of records.

How do I use this to generate a LEFT OUTER JOIN instead?

See also [LEFT OUTER JOIN in Rails 4](http://stackoverflow.com/questions/24358805/left-outer-join-in-rails-4/35363012) — Yarin, Feb 12 '16 at 12:54

score 114 · Accepted Answer · edited May 07 '14 at 14:14

114

@posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id").
              joins(:blog).select

edited May 07 '14 at 14:14

John Naegle

8,077
3
38
47

answered Jul 14 '10 at 12:31

Neil Middleton

22,105
18
80
134

3

what if you only wanted the Posts which had no user? – mcr Aug 18 '11 at 04:30
24

@mcr `@posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id").joins(:blog).where("users.id IS NULL").select` – Linus Oleander Dec 01 '11 at 23:15
1

Doesn't select need a param? Shouldn't this be `select('posts.*')`? – Kevin Sylvestre Mar 26 '15 at 20:08
In Rails 3, this is the only way to have true control over your joins and know exactly what's going on. – Joshua Pinter Jan 29 '18 at 16:26

WuTangTan · Answer 2 · 2016-09-29T11:05:11.410

75

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)

edited Sep 29 '16 at 11:05

answered Oct 12 '12 at 14:33

WuTangTan

1,116
10
12

14

From my tests `includes` does not do a join, but a seperate query to get the assosiation. So it avoids N+1, but not in the same way as a JOIN where the records are fetched in one query. – Kris Feb 19 '13 at 11:53
7

@Kris You're right, in a way. It's something you need to watch out for because the `includes` function does both, depending on the context that you're using it in. The Rails guide explains it better than I could if you read the entirety of section 12: http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations – WuTangTan Feb 20 '13 at 15:50
4

This only partly answers the question because `includes` will generate 2 queries instead of a `JOIN` if you don't have a need for the `WHERE`. – Rodrigue Jul 23 '13 at 14:16
14

This will generate a warning in Rails 4 unless you also add `references(:comments)`. Additionally, this will cause all returned comments to be eager loaded in memory due to the `includes`, which is possibly not what you want. – Derek Prior Nov 30 '13 at 01:58
If you put this code in a scope and for some reasons add later a .joins(:comments), the LEFT OUTER JOIN will be converted into an INNER JOIN... – gtournie Nov 04 '15 at 04:03
2

To make this even more "Railsy": `Post.includes(:comments).where(comments: {visible: true})`. This way you also don't need to use `references`. – michael Aug 31 '16 at 07:42

score 11 · Answer 3 · answered Nov 01 '13 at 23:37

11

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.

answered Nov 01 '13 at 23:37

plainjimbo

7,070
9
41
55

score 10 · Answer 4 · edited Oct 18 '15 at 02:39

10

Use eager_load:

@posts = Post.eager_load(:user)

edited Oct 18 '15 at 02:39

K M Rakibul Islam

33,760
12
89
110

answered Nov 14 '14 at 00:17

Ricardo

101
1
2

score 8 · Answer 5 · edited Dec 26 '12 at 19:43

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.

score 7 · Answer 6 · answered Jun 22 '16 at 14:20

7

There is a left_outer_joins method in activerecord. You can use it like this:

@posts = Post.left_outer_joins(:user).joins(:blog).select

answered Jun 22 '16 at 14:20

Ahmad Hussain

2,443
20
27

1

This doesn't seem to exist in Rails 3, which is what the poster is asking for. – cesoid Mar 30 '17 at 14:08
Correct; this was introduced in Rails 5.0.0. – Ollie Bennett Sep 15 '17 at 16:18

score 4 · Answer 7 · edited Oct 10 '16 at 14:20

4

Good news, Rails 5 now supports LEFT OUTER JOIN. Your query would now look like:

@posts = Post.left_outer_joins(:user, :blog)

edited Oct 10 '16 at 14:20

Diego Plentz

6,760
3
30
31

answered Sep 22 '16 at 06:08

Dex

12,527
15
69
90

score 0 · Answer 8 · edited Jul 16 '14 at 11:00

0

class User < ActiveRecord::Base
     has_many :friends, :foreign_key=>"u_from",:class_name=>"Friend"
end

class Friend < ActiveRecord::Base
     belongs_to :user
end


friends = user.friends.where(:u_req_status=>2).joins("LEFT OUTER JOIN users ON users.u_id = friends.u_to").select("friend_id,u_from,u_to,u_first_name,u_last_name,u_email,u_fbid,u_twtid,u_picture_url,u_quote")

edited Jul 16 '14 at 11:00

lurker

56,987
9
69
103

answered Jul 02 '14 at 13:23

Jigar Bhatt

4,217
2
34
42

LEFT OUTER joins in Rails 3

8 Answers8

Linked