CODEWITHSUNDEEP
sqlruby-on-railsrails-activerecord
B-M
B-M

Reputation: 1298

Where with OR condition for the same column in Rails

I'm trying to retrieve all records that have expired_at as nil and expired_at greater than the current time.

I've tried this:

PriceRule.where("expired_at > ? OR expired_at = ?", DateTime.now, nil)

But that is only returning me the records expired_at greater than DateTime.now. Why is the nil being neglected?

Upvotes: 7

Views: 3133

Answers (3)

Eyeslandic
Eyeslandic

Reputation: 14890

You can also take advantage of Rails or here, which will take care of having IS NULL instead of = NULL. I would also recommend using Time.current instead, which has better support for time zones.

PriceRule
  .where(expired_at: Time.current..)
  .or(PriceRule.where(expired_at: nil))

Upvotes: 7

Syed Samiuzzaman
Syed Samiuzzaman

Reputation: 136

I guess you can use the following syntax as well

PriceRule
.where(expired_at: DateTime.now..)
.or(PriceRule.where(expired_at: nil))

Upvotes: 1

Zoran
Zoran

Reputation: 4226

Your current query checks that expired_at is equal to null (via = NULL) which will not evaluate to true. Instead, you want to query that the column is a null value by using IS NULL.

Therefore, the query can be tweaked to the following:

PriceRule.where("expired_at > ? OR expired_at IS NULL", DateTime.now)

Or, if you wish to keep your original argument structure, you can pass nil as before:

PriceRule.where("expired_at > ? OR expired_at IS ?", DateTime.now, nil)

Upvotes: 4

Related Questions