I have a Users model with a self-referential association for tracking when a user was invited by another user. The association is defined like this:
class User < ApplicationRecord
has_many :invited_users, class_name: "User", foreign_key: "invited_by_id"
end
If I run this ruby code:
User.where(id: 118746).where_exists(:invited_users)
the following incorrect SQL is constructed:
[21] pry(main)> User.where(id: 118746).where_exists(:invited_users)
User Exists? (0.9ms) SELECT 1 AS one FROM "users" WHERE "users"."id" = $1 AND ((EXISTS (SELECT 1 FROM "users" WHERE "users"."id" = 118746 AND ("users"."invited_by_id" = "users"."id")))) LIMIT $2 [["id", 118746], ["LIMIT", 1]]
If I run this slightly different ruby code (which you would expect to produce the same result):
User.where_exists(:invited_users).where(id: 118746)
Then the correct SQL is constructed:
[20] pry(main)> User.where_exists(:invited_users).where(id: 118746)
User Exists? (1.6ms) SELECT 1 AS one FROM "users" WHERE ((EXISTS (SELECT 1 FROM "users" WHERE ("users"."invited_by_id" = "users"."id")))) AND "users"."id" = $1 LIMIT $2 [["id", 118746], ["LIMIT", 1]]
User Load (1.8ms) SELECT "users".* FROM "users" WHERE ((EXISTS (SELECT 1 FROM "users" WHERE ("users"."invited_by_id" = "users"."id")))) AND "users"."id" = $1 [["id", 118746]]
So it looks like the order of where(id: 118746) and where_exists(:invited_users) is affecting the output.
I have a Users model with a self-referential association for tracking when a user was invited by another user. The association is defined like this:
If I run this ruby code:
User.where(id: 118746).where_exists(:invited_users)
the following incorrect SQL is constructed:If I run this slightly different ruby code (which you would expect to produce the same result):
User.where_exists(:invited_users).where(id: 118746)
Then the correct SQL is constructed:So it looks like the order of
where(id: 118746)
andwhere_exists(:invited_users)
is affecting the output.@EugZol