`Cannot visit Arel::SelectManager` when counting a join to a subselect

[shepmaster]

It's entirely possible that the title isn't comprehensive enough, but it's a close start...

For full reproduction, check out my repo with all the models.

Synopsis

I have a scope that returns Users that have an Address. I want to get a list of all users and include a new column indicating if the user has an address:

def self.has_address
  joins { addresses }
end

def self.with_has_address
  a = User.has_address
  joins { a.as('addressable').on { id.eq(addressable.id) }.outer }
    .select { ["users.*", addressable.id.as('addressable')] }
end

This code works fine, until I attempt to get the count of users:

User.with_has_address
# SELECT users.*, "addressable"."id" AS addressable
# FROM "users"
# LEFT OUTER JOIN (
#   SELECT "users".* FROM "users"
#   INNER JOIN "addresses" ON "addresses"."user_id" = "users"."id"
# ) addressable
# ON "users"."id" = "addressable"."id"
#
# => #<ActiveRecord::Relation [#<User id: 1, created_at: "2014-10-23 15:41:43", updated_at: "2014-10-23 15:41:43", active: nil, parent_id: nil>]>

User.with_has_address.count
# => TypeError: Cannot visit Arel::SelectManager

[teliosdev]

The problem lies here. join.subquery.left.arel returns a Arel::SelectManager, which is not acceptable to Arel. I'm not sure what should be given in its place, though.

[teliosdev]

I made this modification and it seems to work:

--- a/lib/squeel/adapters/active_record/4.1/relation_extensions.rb
+++ b/lib/squeel/adapters/active_record/4.1/relation_extensions.rb
@@ -301,7 +301,7 @@ module Squeel
           def build_join_from_subquery(subquery_joins)
             subquery_joins.map do |join|
               join.type.new(
-                Arel::Nodes::TableAlias.new(join.subquery.left.arel, join.subquery.right),
+                Arel::Nodes::TableAlias.new(Arel::Nodes::Grouping.new(join.subquery.left.arel.ast), join.subquery.right),
                 Arel::Nodes::On.new(where_visit(join.constraints))
               )
             end

[mchavarriagam]

@medcat, your patch fixed the issue for me, thanks. @bigxiang: I wrote a test for this and it passes. I can make a pull request if you guys want. Here's the commit for @medcat's patch in case you want to take a look at it.

edit: after running the Travis CI builds, I see that the test is breaking for Rails < 4.0. I'll do some debugging to see if there's a workaround for that.

[shepmaster]

@medcat @mchavarriagam thanks so much for working on this! I wish I could remember exactly where I had this in our production code so I could give it a shot :smile_cat:

However, it does look like it fixes the issue I created in the repo in the first comment, so that's super promising!

[danielnc]

This commit breaks 4.2 subquery bind_params

            notes_relation = Person.first.notes
            klazz_name = "Person"
            relation = Person.joins { notes_relation.as("notes").on { (~id == notes.notable_id) & (notes.notable_type == klazz_name) }.outer }.where { notes.note != nil }

bind values for $1 and $2 are missing and query fails

[danielnc]

I've implemented a quick hack that seems to solve this issue Help is appreciated: https://github.com/BraneLabs/squeel/commit/970ef16d04758422306217a6cc3d8486c6a7360a

Fixes #344 too

[rapides]

Is it possible to merge that with master?