Adam
Adam

Reputation: 2997

How to specify a less than today condition on a date in Rails ActiveRecord

I'm trying to figure out how to pull all the records in my set where their fields publish is true and expires is less than today. I have the following but I don't think the less than part is working, can someone please point me on the right track?

Announcement.where(publish: true, :expires < Date.today)

Thanks in advance for your help

Upvotes: 47

Views: 49654

Answers (4)

Sergio Belevskij
Sergio Belevskij

Reputation: 2957

You would to using interval for this. Your date/time can be limited by interval of dates/times:

time_from = Time.current - 5.years
time_to = Time.current - 1.year
adult_citties = ::Cat.where(date_birthday: time_from..time_to)

When you not need to limit bottom or upper border, you an omit its values:

old_kitties = ::Cat.where(date_birthday: nil..time_to)
young_kitties = ::Cat.where(date_birthday: time_from..nil)

UPD: also you can omit nil too:

old_kitties = ::Cat.where(date_birthday: ..time_to)
young_kitties = ::Cat.where(date_birthday: time_from..)

Upvotes: 3

Martin Konecny
Martin Konecny

Reputation: 59691

Posting this a few years after the other answers, but this is the cleanest approach IMO:

Announcement.where(publish: true, expires: ...Date.today)

Upvotes: 8

leonardoborges
leonardoborges

Reputation: 5629

Try this:

Announcement.where("publish = ? AND expires < ?", true, Date.today)

Upvotes: 65

wycleffsean
wycleffsean

Reputation: 1377

Since this comes up first on google I thought I'd chime in. It's probably better to rely on Arel in this circumstance.

expires = Announcement.arel_table[:expires]
@announcements = Announcement.where(:publish => true)
  .where(expires.lt(Date.today))

That will build the following SQL query

-- Using Arel
SELECT "announcements".* FROM "announcements" 
WHERE "announcements"."publish" = 't' 
  AND ("announcements"."expires" < '2016-02-03 18:41:26.454325')

-- Contrast that with the string binding method mentioned above
SELECT "announcements".* FROM "announcements"
WHERE (publish = 't' AND expires < '2016-02-03 18:41:26.454325')

The column names are fully qualified, so you will avoid conflicts when composing other queries on top of this ActiveRecord::Relation i.e @announcements

Upvotes: 34

Related Questions