Using any_of with an INNER JOIN relation results in incorrect query

[dv]

Trying to do this:

User.where.any_of(firm.users.admins, company.role_users)

With these classes (abbreviated):

class User < ActiveRecord::Base
  scope :admins, -> { where(firm_role: 'admin') }
end

class Firm < ActiveRecord::Base
  has_many :users
end

class Company < ActiveRecord::Base
  has_many :roles
  has_many :role_users, through: :roles, source: :user
end

class Role < ActiveRecord::Base
  belongs_to :company
  belongs_to :user
end

The query it ends up with is as follows:

SELECT "users".* FROM "users" INNER JOIN "roles" ON "users"."id" = "roles"."user_id" WHERE (("users"."firm_id" = 123 AND "users"."firm_role" = 'admin' OR "roles"."company_id" = 456))

Note the INNER JOIN in the outer query. This is not the query one would expect, since it limits the results to company.role_users. Might as well have used #merge instead.

This workaround works correctly:

User.where.any_of(firm.users.admins, id: company.role_users)

Which results in:

SELECT "users".* FROM "users" WHERE (("users"."firm_id" = 123 AND "users"."firm_role" = 'admin' OR "users"."id" IN (SELECT "users"."id" FROM "users" INNER JOIN "roles" ON "users"."id" = "roles"."user_id" WHERE "roles"."company_id" = 456)))

Note the INNER JOIN is now in the subquery.

[oelmekki]

Hi @dv,

Can you please include the definition of the User.admins scope in your code?

Thanks,

[dv]

Hey @oelmekki sure thing - I update the original code in the post above.

[shekibobo]

This is also happening if using joins(:things).merge(Thing.other_scope).

[oelmekki]

Hi guys, sorry not to got back earlier, I handle way too many projects, and this issue is tricky :)

The problem is when using JOIN statements as condition, typically:

SELECT "users".* FROM "users" INNER JOIN "roles" ON "users"."id" = "roles"."user_id"

This kind of condition can't actually be OR'd. For long, my hope was to have proper support of UNION in arel, but it seems like it won't happen.

I like your workaround, David. I could not recommend using it because it relies on AR implementation rather than on its API, so it's not guaranteed it will be stable on the long run. But what it does is interesting: using a sub query to include the join in the OR statement. I think that's something I can reproduce internally.