CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Qumber ali
Qumber ali

Reputation: 23

ActiveRecord Query on text field PostgreSQL

I have a table Products and around 58000 records in it. I run two queries are as follows.

Product.where(description: 'swiss').count

It returns 0 products out of 58000 products. But when I run query below.

Product.where.not(description: 'swiss').count

it returns 2932 products out of 58000 products. I think it should return all 58000 products, because it is the reverse of first query. I did not understand why it returns only 2932 products.

Upvotes: 0

Views: 304

Answers (2)

Fire Lancer
Fire Lancer

Reputation: 30145

If you have NULL values in your columns, this could happen, because NULL always compares as NULL, even to itself, but WHERE expression must be true to include a result.

'a' = 'a'; True
'a' = 'b'; False
'a' = NULL; Results in NULL (not false!)
NULL = NULL; Results in NULL (not true!)
'a' != 'a'; False
'a' != 'b'; True
'a' != NULL; NULL

e.g. consider the table null_test containing

 id | str
----+-----
  1 | <NULL>
  2 | a
  3 | b

When looking for a column equal to some value, the NULL is never equal, so this will just return row 2.

SELECT id FROM null_test WHERE str = 'a';

But the same applies looking for a column not equal to some value, the NULL is never not equal (its NULL), so this will just return row 3.

SELECT id FROM null_test WHERE str != 'a';

Thus the total of your = and != is not all the rows, because you never included the NULL rows. This is where IS NULL and IS NOT NULL come in (they work like you might expect = NULL and != NULL to work).

SELECT id FROM null_test WHERE str != 'a' OR str IS NULL;

Now you get rows 1 and 3, making this the opposite of str = 'a'.

For ActiveRecord, it treats the Ruby nil value like the SQL NULL and creates the IS NULL checks in certain situations.

NullTest.where(str: 'a')
SELECT * FROM "null_test" WHERE "str" = 'a'

NullTest.where.not(str: 'a')
SELECT * FROM "null_test" WHERE "str" != 'a'

NullTest.where(str: nil)
SELECT * FROM "null_test" WHERE "str" IS NULL

NullTest.where.not(str: nil)
SELECT * FROM "null_test" WHERE "str" IS NOT NULL

NullTest.where(str: nil).or(NullTest.where.not(str: 'a'))
SELECT * FROM "null_test" WHERE "str" IS NULL OR "str" != 'a'

Upvotes: 1

SteveTurczyn
SteveTurczyn

Reputation: 36860

You have likely many records where description is nil and those are not included in the not.

A way to include the 'nil' records as well would be...

Product.where('description <> ? OR description IS NULL', 'swiss')

Or alternatively

Product.where.not(description: 'swiss').or(Product.where(description: nil))

Upvotes: 0

Related Questions