Rails Query nil problems

Question

I'm trying to get all the coupons with an expirationDate(date type) that are nil:

  @coupons = @deal.coupons.all 
  @coupons = @coupons.where("expirationDate = ?", nil)

But for some reason the code above doesn't work. However, if instead I do:

@coupons = @deal.coupons.all
@coupons = @coupons.where(expirationDate: nil)

It gets all the nil coupons as I want it to. I'm confused as to why the first one doesn't work, but the second one does. Any help would be appreciated.

score 3 · Answer 1 · edited May 23 '17 at 12:03

3

for exampel.

User.where("name = ?",nil)

sql output :

SELECT "users".* FROM "users"  WHERE (name = NULL)

The other format:

u = User.where(name: nil)

sql output:

SELECT "users".* FROM "users"  WHERE "users"."name" IS NULL

You will find the sql different. Detail for SQL is null and = null [duplicate]

edited May 23 '17 at 12:03

Community

1
1

answered Nov 14 '15 at 02:28

kai

310
3
14

score 2 · Accepted Answer · edited May 23 '17 at 10:27

The SQL query for NULL or NOT NULL is as following:

expirationDate IS NULL or expirationDate IS NOT NULL

It doesn't work with equal "=" sign. You can't do in SQL as expirationDate = NULL

You could try like this as well

@coupons = @coupons.where("expirationDate IS NULL")

You can take a look at this answer for other alternatives of using null in condition https://stackoverflow.com/a/4252615/343679

Rails Query nil problems

2 Answers2