CODEWITHSUNDEEP
ruby-on-railsactiverecordrails-activerecord
dtc
dtc

Reputation: 10296

How does this ActiveRecord 'where' clause work?

I have this statement:

myuser.orders.exists?(['(orderstatus = ?) ', statusid])

It returns true since there is an orderstatus that matches the statusid.

Next I have:

myuser.orders.where('id not in (?)', nil).exists?(['(orderstatus = ?) ', statusid])

This returns false where I thought it might return true since there are no ids that are nil.

Then I have:

myuser.orders.where(nil).exists?(['(orderstatus = ?) ', statusid])

This returns true.

My question is why does the middle statement return false? It doesn't complain or throw any errors. I guess I'm using nil wrong, but can someone explain?

Upvotes: 1

Views: 1390

Answers (1)

mu is too short
mu is too short

Reputation: 434735

You're having trouble with SQL's NULL. The where in the middle one:

where('id not in (?)', nil)

becomes this SQL:

id not in (null)

and that's equivalent to this:

id != null

But the result of id != null is neither true nor false, the result is NULL and NULL in a boolean context is false; in fact, x = null and x != null result in NULL for all x (even when x itself is NULL); for example, in PostgreSQL:

=> select coalesce((11 = null)::text, '-NULL-');
 coalesce 
----------
 -NULL-
(1 row)

=> select coalesce((11 != null)::text, '-NULL-');
 coalesce 
----------
 -NULL-
(1 row)

=> select coalesce((null = null)::text, '-NULL-');
 coalesce 
----------
 -NULL-
(1 row)

=> select coalesce((null != null)::text, '-NULL-');
 coalesce 
----------
 -NULL-
(1 row)

MySQL and every other reasonably compliant database will do the same thing (with possibly different casting requirements to make the NULL obvious).

The result is that where(id not in (?)', nil) always yields an empty set and your existence check will always fail on an empty set.

If you want to say "all the rows where id is not NULL" then you want to say:

where('id is not null')

If your id is a primary key (as it almost certainly is), then id will never be NULL and you can leave that where out completely.

When you hand where just a nil:

where(nil)

where's argument parsing logic will ignore the nil completely and where(nil) will be the same as where() and where() does nothing at all to the query. The result is that the first and third queries are identical as far as the database is concerned.

Upvotes: 4

Related Questions