CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Mike H-R
Mike H-R

Reputation: 7815

rails ActiveRecord can you merge scopes that have joins within them?

I am wanting to join a single table to another table with a specific scope.

What I had tried to do does not work, but a version using sub-select works correctly.

MyModel belongs_to a User class (which has_one MyModel) and the user class has a scope that involves joins to other tables.

What I am trying to do is:

# doesn't work, gives user that is not inside the merged scope
MyModel.joins(:user).merge(User.has_accounts)

This generates the following SQL which I think should do what I want:

SELECT DISTINCT "my_model".* FROM
"my_model" INNER JOIN "users" ON "users"."id" = 
"my_model"."user_id" LEFT OUTER JOIN "logins" ON 
"logins"."user_id" = "users"."id" LEFT OUTER JOIN "logins" 
"logins_users_join" ON "logins_users_join"."user_id" = "users"."id" 
LEFT OUTER JOIN "accounts" ON "accounts"."login_id" = 
"logins_users_join"."id"

The user scope "has_accounts" has some joins inside it:

scope :has_accounts, -> { joins(:logins).joins(:accounts).distinct }

I am able to write write the query correctly using a sub-select like so:

# works does not give my_model that has a user associated that is not in the scope
MyModel.where(user_id: User.has_accounts.select(:id))

The relevant parts of the model are as follows:

class MyModel
  belongs_to :user
end

class User
  has_one :my_model
  has_many :logins
  has_many :accounts, through: :logins
  scope :has_accounts, -> { joins(:logins).joins(:accounts).distinct }
end

class Login
  belongs_to :user
  has_many :accounts
end

class Account
  belongs_to :login
end

what am I doing wrong with my join and merge so that it does not give the correct result?

Can anyone see what it is in the generated SQL that means that the users that do not have an associated account are present in the result?

Upvotes: 1

Views: 2209

Answers (1)

Mike H-R
Mike H-R

Reputation: 7815

After digging into this for a while it seems that it is a bug in activerecord. It is referred to in this issue:

https://github.com/rails/rails/issues/16140

and it is implied that it is a bug due to this PR:

https://github.com/rails/rails/pull/26195

The bug causes OUTER JOINS to be generated on merging something with joins into another query, instead of INNER JOINS

Upvotes: 1

Related Questions