Nikita Nikita - 3 months ago 8
Ruby Question

Rails WHERE query on has_many relation works incorrect?

Cheers!
Let's say I have

User
that
has_many
Accounts
,
Account
model has
logged_in (boolean)
field.

I wrote a AR query:
User.joins(:accounts).where(accounts: { logged_in: false })
, right?

But then I check it
User.joins(:accounts).where(accounts: { logged_in: false }).first.accounts.pluck(:logged_in)

And could see that there are some accounts with
mobiles.logged_in = true
values.

(0.1ms) SELECT "accounts"."logged_in" FROM "accounts" WHERE "accounts"."user_id" = $1 [["user_id", 373]]
false
false
true


Why?

Answer
User.joins(:accounts).where(accounts: { logged_in: false })

fetches all users that have at least one account with logged_in == false.
That's because it gets translated to (roughly):

SELECT users.*
FROM users
INNER JOIN accounts
WHERE users.id = accounts.user_id
  AND NOT accounts.logged_in

The problem is that when you do

User.joins(:accounts).where(accounts: { logged_in: false }).first.accounts

you're actually re-fetching all accounts for the first user you found, logged_in or not.
This user does have at least one NOT logged_in account, but not necessarily all of them.

Comments