CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
sara lance
sara lance

Reputation: 603

Understanding query method where in ActiveRecord

I want to return the count of users that that have a boolean value, show, not set to true. show is a boolean but is initialized to nil.

Because of this I'm getting some results that I did not expected. For example,

[{show: nil}].where.not(show: true).count -> 0

[{show: nil}].where(show: [nil, false]).count -> 1

Shouldn't this query's return the same thing?

Upvotes: 0

Views: 75

Answers (1)

engineersmnky
engineersmnky

Reputation: 29478

This is a general misunderstanding of NULL (which is not uncommon by the way).

NULL is not comparable, it is neither true or false, nor equal or not equal, nor less than or greater than, etc., NULL cannot even be compared to NULL. e.g. NULL = NULL is false but NULL != NULL is also false.

Your First case (.where.not(show: true)) is show != true (or NOT(show =true)) which is the equivalent of show = false.

Your second example (.where(show: [nil, false])) is show IS NULL OR show = false. While this syntax show: [x,y] usually results in an IN() clause Arel is smart enough to realize that one of these values is nil and converts the SQL generation accordingly because show IN (NULL,false) would be equivalent to show = NULL OR show = false (this is how IN() works) but as mentioned NULL = NULL is false thus why IS NULL and IS NOT NULL exist.

The result of your query is because there are no rows where show = false but there is one row where show IS NULL.

Upvotes: 5

Related Questions