added ActiveRecord::Relation#left_outer_joins #12071

Crunch09 · 2013-08-29T09:25:14Z

Hi,

this adds the ability to use a left outer join query without writing pure SQL as an argument to #joins and also query for a specific selection of columns which doesn't work with #includes.

The ability to do this is already implemented in ActiveRecord::Associations::JoinDependency#build but it was only used with Arel::InnerJoin as join_type.
I am not quite sure about the tests, i just used the InnerJoin tests as a starting point. Any help would be appreciated.

Example:

  User.outer_joins(:posts)
  => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

provideal · 2013-08-29T09:27:42Z

+1

Mario1988 · 2013-08-29T10:28:46Z

+1

Kleinitay · 2013-08-30T10:16:29Z

+1

njakobsen · 2013-08-30T23:08:50Z

+1 @Crunch09 Your patch is so much more pro than my poor man's outer_join where I gsub the inner joins to outer joins. https://gist.github.com/njakobsen/6395146

egilburg · 2013-08-31T06:40:42Z

+1

robin850 · 2013-08-31T14:56:23Z

Thanks for your contribution @Crunch09! Actually, it would be awesome if we could update a little bit the guides with this new feature.

Crunch09 · 2013-08-31T19:36:35Z

yes, will write something tomorrow

frodsan · 2013-08-31T22:04:46Z

activerecord/lib/active_record/relation/query_methods.rb

@@ -948,6 +983,11 @@ def build_joins(manager, joins)
        end
      end

+      build_join_query(manager, buckets, Arel::InnerJoin)
+


thank you, fixed that!

Vetal4eg · 2013-09-01T13:17:37Z

+1

csouls · 2013-09-01T14:31:08Z

+1

Crunch09 · 2013-09-01T22:11:29Z

@robin850 Added few sentences in the AR-querying guide about it and also rebased against current master.

robin850 · 2013-09-07T13:53:12Z

@Crunch09 : Awesome, thank you! ❤️

@rafaelfranca : Could you please have a look ? It looks awesome to me!

Crunch09 · 2013-09-26T12:38:31Z

@rafaelfranca @robin850 I just rebased again against current master, anything more i can do?

dmathieu · 2013-09-26T12:40:30Z

cc @tenderlove @jonleighton

toreriklinnerud · 2013-10-10T00:20:39Z

+1 Outer joins is one of the few remaining things that force us to fall back to SQL in our codebase

tenderlove · 2014-04-23T20:37:46Z

activerecord/lib/active_record/relation/query_methods.rb

+      build_join_query(manager, buckets, Arel::InnerJoin)
+    end
+
+    def build_join_query(manager, buckets, join_type=nil)


Please do not default join_type to nil. I don't think nil is actually an acceptable value. Change the things that call build_join_query to actually pass in the things it needs.

tenderlove · 2014-04-23T20:39:30Z

Can you please change these tests not to use to_sql? We have a capture_sql command that you can use to make assertions against the sql run against the database.

tenderlove · 2014-04-23T20:40:58Z

I'm positive on this, but the copy/paste make me uneasy. :(

matthewd · 2014-04-23T21:19:54Z

guides/source/active_record_querying.md

@@ -937,11 +938,17 @@ This will result in the following SQL:
 SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id
 ```

-### Using Array/Hash of Named Associations
+Instead of writing raw SQL in this case you could also use the `outer_joins` method
+which would produce the same result.


If we're going to introduce an outer_joins, I think this is a bit of an "also-ran" position to mention it... we should probably be offering it before we suggest raw SQL.

In fact, maybe the SQL should change, to show a still-legitimate use case for a raw JOIN, as it previously did, instead of an inferior spelling of built-in functionality.

matthewd · 2014-04-23T21:23:24Z

I'm vaguely wary of outer_joins == LEFT OUTER JOIN. With includes, that's abstracted-away implementation detail. But here...

Maybe consider left_joins?

tenderlove · 2014-04-24T17:09:39Z

I'm vaguely wary of outer_joins == LEFT OUTER JOIN. With includes, that's abstracted-away implementation detail. But here...

Maybe consider left_joins?

Good point. If it's actually going to do an LOJ, the method should probably be left_joins or left_outer_joins.

Crunch09 · 2014-04-24T22:11:39Z

Thank you for your feedback! I'll update this PR over the weekend and report back then

al2o3cr · 2014-04-25T14:50:58Z

One thing I'd love to see but don't have a clear API for - the ability to specify additional bits in the ON clause. Perhaps something like:

User.outer_joins(:posts) { where(featured: true) }

which would produce the SQL:

SELECT ... FROM users
LEFT OUTER JOIN posts ON posts.user_id = users.id AND posts.featured = 't'

assuming there's a boolean featured column on posts.

Note that the above is not equivalent to:

User.outer_joins(:posts).where(featured: true)

since that query doesn't include users who don't have any featured posts.

Without this feature, it would still be possible to create the desired SQL with a scoped association:

# on User
has_many :featured_posts, -> { where(featured: true) }, class_name: 'Post'

User.outer_joins(:featured_posts)

but that method doesn't exactly scale to arbitrary sets of conditions.

matthewd · 2014-04-25T15:54:17Z

@al2o3cr +1. We should probably address that separately, afterwards, though... whatever we end up with seems like it should apply to includes as well. This would address one of the biggest causes of scoped-association proliferation that I see. (:through targets being the other.)

njakobsen · 2014-11-13T00:52:47Z

Any word on when this will be merged? It's been a long time in coming, and every project I make ends up needing left outer joins.

Crunch09 · 2014-11-13T08:32:39Z

it's my fault. There were lots of changes in the affected code and i need to solve the merge conflicts and also apply the feedback. I started with that and i hope i can finish it this weekend, sorry about the long wait.

Crunch09 · 2015-01-15T08:31:36Z

@k0kubun Good point! I would be fine either way.
@tenderlove I don't know if you remember, but we talked briefly at Arrrcamp about this 😄 I rebased it against current master, would be great if you could take another look if you have the time 😉

dmitry · 2015-02-25T09:44:27Z

@al2o3cr have you already created separate issue for supporting the User.outer_joins(:posts) { where(featured: true) }?

njakobsen · 2015-03-06T01:53:33Z

Doh. I needed a left outer join again today.

Example: User.left_outer_joins(:posts) => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

added ActiveRecord::Relation#outer_joins

This assumes only one query was ever executed, but it appears to sometimes be loading schema information. We can just look at the array of queries, rather than the "first" one that was run

egilburg · 2015-10-30T17:38:35Z

🎉

thibaudgg · 2015-10-31T19:43:40Z

👏 👏

Crunch09 · 2015-10-31T19:46:26Z

Thank you all ❤️ So happy this got merged!

njakobsen · 2015-10-31T22:24:32Z

👏👏

frodsan reviewed Aug 31, 2013
View reviewed changes

tenderlove reviewed Apr 23, 2014
View reviewed changes

matthewd reviewed Apr 23, 2014
View reviewed changes

matthewd mentioned this pull request Apr 24, 2014

Rails 3.2.0 + mysql2 0.3.11 + Model.select("some joined col.").includes = FAIL #4564

Closed

chancancode added the activerecord label Apr 24, 2014

robin850 added the needs feedback label Apr 25, 2014

rafaelfranca added this to the 5.0.0 milestone Jan 2, 2015

Crunch09 force-pushed the outer_joins branch from 496801d to 5df2f16 Compare January 14, 2015 20:07

Crunch09 force-pushed the outer_joins branch from 5df2f16 to 304e91a Compare February 6, 2015 22:59

rails locked and limited conversation to collaborators Mar 10, 2015

added ActiveRecord::Relation#left_outer_joins

3f46ef1

Example: User.left_outer_joins(:posts) => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

Crunch09 force-pushed the outer_joins branch from 304e91a to 3f46ef1 Compare May 19, 2015 08:44

rails unlocked this conversation Sep 10, 2015

matthewd mentioned this pull request Sep 10, 2015

references() overrides select() #21578

Closed

rails-bot assigned sgrif Oct 20, 2015

sgrif merged commit 3f46ef1 into rails:master Oct 30, 2015

sgrif mentioned this pull request Oct 30, 2015

[Meta] Rails 5.0 Release Notes #20612

Closed

sgrif added a commit that referenced this pull request Oct 30, 2015

Merge pull request #12071 from Crunch09/outer_joins

e038975

added ActiveRecord::Relation#outer_joins

k0kubun mentioned this pull request Oct 30, 2015

Alias left_joins to left_outer_joins #22125

Merged

Crunch09 deleted the outer_joins branch October 31, 2015 19:46

rafaelfranca modified the milestones: 5.0.0 [temp], 5.0.0 Dec 30, 2015

prathamesh-sonpatki changed the title ~~added ActiveRecord::Relation#outer_joins~~ added ActiveRecord::Relation#left_outer_joins Mar 10, 2016

khiav223577 mentioned this pull request Dec 25, 2017

implement left joins khiav223577/left_joins#1

Merged

This was referenced Aug 22, 2022

ar_outer_joins dependency? GeorgeKaraszi/ActiveRecordExtended#70

Closed

Remove ar_outer_joins. Not needed since Rails 5.0 GeorgeKaraszi/ActiveRecordExtended#85

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

added ActiveRecord::Relation#left_outer_joins #12071

added ActiveRecord::Relation#left_outer_joins #12071

Crunch09 commented Aug 29, 2013

provideal commented Aug 29, 2013

Mario1988 commented Aug 29, 2013

Kleinitay commented Aug 30, 2013

njakobsen commented Aug 30, 2013

egilburg commented Aug 31, 2013

robin850 commented Aug 31, 2013

Crunch09 commented Aug 31, 2013

frodsan Aug 31, 2013

Crunch09 Sep 1, 2013

Vetal4eg commented Sep 1, 2013

csouls commented Sep 1, 2013

Crunch09 commented Sep 1, 2013

robin850 commented Sep 7, 2013

Crunch09 commented Sep 26, 2013

dmathieu commented Sep 26, 2013

toreriklinnerud commented Oct 10, 2013

tenderlove Apr 23, 2014

tenderlove commented Apr 23, 2014

tenderlove commented Apr 23, 2014

matthewd Apr 23, 2014

matthewd commented Apr 23, 2014

tenderlove commented Apr 24, 2014

Crunch09 commented Apr 24, 2014

al2o3cr commented Apr 25, 2014

matthewd commented Apr 25, 2014

njakobsen commented Nov 13, 2014

Crunch09 commented Nov 13, 2014

Crunch09 commented Jan 15, 2015

dmitry commented Feb 25, 2015

njakobsen commented Mar 6, 2015

egilburg commented Oct 30, 2015

thibaudgg commented Oct 31, 2015

Crunch09 commented Oct 31, 2015

njakobsen commented Oct 31, 2015

added ActiveRecord::Relation#left_outer_joins #12071

added ActiveRecord::Relation#left_outer_joins #12071

Conversation

Crunch09 commented Aug 29, 2013

provideal commented Aug 29, 2013

Mario1988 commented Aug 29, 2013

Kleinitay commented Aug 30, 2013

njakobsen commented Aug 30, 2013

egilburg commented Aug 31, 2013

robin850 commented Aug 31, 2013

Crunch09 commented Aug 31, 2013

frodsan Aug 31, 2013

Choose a reason for hiding this comment

Crunch09 Sep 1, 2013

Choose a reason for hiding this comment

Vetal4eg commented Sep 1, 2013

csouls commented Sep 1, 2013

Crunch09 commented Sep 1, 2013

robin850 commented Sep 7, 2013

Crunch09 commented Sep 26, 2013

dmathieu commented Sep 26, 2013

toreriklinnerud commented Oct 10, 2013

tenderlove Apr 23, 2014

Choose a reason for hiding this comment

tenderlove commented Apr 23, 2014

tenderlove commented Apr 23, 2014

matthewd Apr 23, 2014

Choose a reason for hiding this comment

matthewd commented Apr 23, 2014

tenderlove commented Apr 24, 2014

Crunch09 commented Apr 24, 2014

al2o3cr commented Apr 25, 2014

matthewd commented Apr 25, 2014

njakobsen commented Nov 13, 2014

Crunch09 commented Nov 13, 2014

Crunch09 commented Jan 15, 2015

dmitry commented Feb 25, 2015

njakobsen commented Mar 6, 2015

egilburg commented Oct 30, 2015

thibaudgg commented Oct 31, 2015

Crunch09 commented Oct 31, 2015

njakobsen commented Oct 31, 2015