How can I do a LEFT OUTER JOIN using Rails ActiveRecord?

Question

I don't have any ideas. Could you give me any clues (like reference sites). Any help will be appreciated.

Model1: GROUP(id, name)
Model2: USER_GROUP_CMB(id, user_id, group_id)

Expected SQL statement:

SELECT * 
FROM groups AS g LEFT OUTER JOIN user_group_cmbs AS cmb 
            ON g.id = cmb.group_id
WHERE cmb.user_id = 1

I tried to set up associations below but I dont know what to do after this.

class Group < ActiveRecord::Base
  has_many :user_group_cmb
end

class UserGroupCmb < ActiveRecord::Base
  has_many :group
end

Rails Version: 3.1.1

possible duplicate of [LEFT OUTER joins in Rails 3](http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3) — , Aug 09 '13 at 16:05
Use [Squeel](https://github.com/activerecord-hackery/squeel#joins) (See [LEFT OUTER JOIN in Rails 4](http://stackoverflow.com/a/35363012/165673)) — Yarin, Feb 12 '16 at 12:49
There's an out-of-the-box solution for Rails 5! See [answer](https://stackoverflow.com/a/35592251/1323144) below... — Ollie Bennett, Sep 15 '17 at 16:16

score 34 · Accepted Answer · edited Sep 07 '17 at 14:41

34

I believe an includes will use a LEFT OUTER JOIN query if you do a condition on the table that the includes association uses:

Group.includes(:user_group_cmb).where(user_group_cmbs: { user_id: 1 })

edited Sep 07 '17 at 14:41

siegy22

4,295
3
25
43

answered Nov 06 '11 at 05:59

Ryan Bigg

106,965
23
235
261

7

Doesn't work here (Rails 3.2.7): using a condition on an `includes`-referenced relation raises a `StatementInvalid` exception, complaining it misses a `FROM-clause entry`. I need to add an explicit call to `join`, which defaults to being inner. – MattiSG Jan 14 '13 at 16:12
What are your thoughts about http://stackoverflow.com/questions/8025429/rails-how-can-i-do-left-outer-join-on-activerecord/14599174#answer-14599174 ? – Jan 30 '13 at 08:01
1

If you're worried about filtering, then define another association that links to the same class and which applies the filtering for you. – Ryan Bigg Jan 30 '13 at 20:27
2

If you really hate strings and like symbols and hashes (not rockets), you can do ```Group.includes(:user_group_cmb).where(user_group_cmbs: { user_id: 1})``` – marksiemers May 14 '15 at 20:27
1

Or: `Group.eager_load(:user_group_cmb).where('user_group_cmbs.user_id = ?', 1)`. `eager_load` always performs left join (while `includes` performs two separate queries instead by default). But be careful using left join generated with `includes` or `eager_load` since it can break custom `select` clause. – Marek Lipka Mar 07 '16 at 11:49
@marksiemers I love ActiveRecord for this. (Question is edited too) – siegy22 Sep 07 '17 at 14:42

Diego Plentz · Answer 2 · 2017-01-12T19:22:11.590

20

Rails 5+ allows you to do the following:

Group.left_outer_joins(:user_group_cmb)

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-left_joins

edited Jan 12 '17 at 19:22

answered Feb 24 '16 at 02:39

Diego Plentz

6,760
3
30
31

score 10 · Answer 3 · answered Jul 19 '13 at 09:22

10

You can do this in rails 3.x regardless if you are referencing the table or not in a where clause:

Group.eager_load(:user_group_cmb)

...and it will perform a left outter join

answered Jul 19 '13 at 09:22

knoopx

17,089
7
36
41

score 8 · Answer 4 · answered Jan 30 '13 at 08:03

It might be quite important to mention that using includes has possibly unwanted side-effects.

if the filtered association is subsequently scoped, all of the original filtering disappears

As it turns out, by scoping on the filtered association we’ve lost any filtering-as-side-effect that we attained from includes. And it’s not because of how we searched, either.

Make sure to read the complete article and alternatively there's a gem for that.

score 8 · Answer 5 · answered Sep 30 '15 at 11:23

The problem with the accepted answer is that it will actually do a LEFT INNER JOIN technically because it won't display entries where the user_group_cmbs. user_id is null (which would be the reason to do a LEFT OUTER JOIN).

A working solution is to use #references:

Group.includes(:user_group_cmb).references(:user_group_cmb)

Or the even more convenient #eager_load:

Group.eager_load(:user_group_cmb)

Read the more detailed explanation here.

score 7 · Answer 6 · answered Apr 18 '13 at 10:55

7

Use a custom joins statement:

Group.joins("left outer join user_group_cmbs as cmb on groups.id = cmb.group_id")
  .where("cmb.user_id = ?", 1)

answered Apr 18 '13 at 10:55

hammady

969
1
13
22

score 2 · Answer 7 · answered Nov 06 '11 at 06:54

2

Use has_and_belongs_to_many if you just need to link users to groups. Use has_many :through if you need to store additional membership information.

Example:

class User < ActiveRecord::Base
  has_and_belongs_to_many :groups
end

class Group < ActiveRecord::Base
  has_and_belongs_to_many :users      
end

answered Nov 06 '11 at 06:54

panupan

1,212
13
15

Exactly, I must concern about User as you said. I will try it. – zono Nov 06 '11 at 07:13

score 0 · Answer 8 · answered May 15 '15 at 15:00

Couldn't comment to an earlier answer because well I don't have 50 reputation. But here's how it worked for me when I ran into below error

includes-referenced relation raises a StatementInvalid exception, complaining it misses a FROM-clause entry. I need to add an explicit call to join, which defaults to being inner.

I had to use .references and mention the table name or for it get added to the FROM-clause

How can I do a LEFT OUTER JOIN using Rails ActiveRecord?

8 Answers8

Linked