I have a model, Lease, with attributes ends_on (date), and notice_days (integer) I would like to write a query that returns all records that have an ends_on date that is less than today's date plus notice_days for that record. In straight SQL (for my Postgresql db) the query is:
SELECT * FROM leases WHERE ends_on < CURRENT_DATE notice_days
I can easily write a query to compare against today's date some fixed buffer
Lease.where(ends_on: ...Date.current 30).all
But I don't want the fixed buffer, I want a buffer (notice_days) that is unique to each record.
I do realize that I could execute the sql shown above using exec_sql, but I would like to learn how to do this with a straightforward ActiveRecord query.
Edit
Stefanyuk's answer below does in fact answer my question, but I am looking for a way to write the ActiveRecord query with key value pairs as arguments to the where method in the hopes of making the query less database dependent. It seems that this should be possible but I have tried many ways of passing the attribute name without success.
This does not work, but I am looking for something similar that does:
Lease.where(ends_on: ...(Date.current :notice_days))
Is there a way to replace the symbol :notice_days in that example with a reference to the attribute notice_days?
CodePudding user response:
You can do this by using interval
SELECT * FROM leases WHERE ends_on < CURRENT_DATE interval '1 day' * notice_days
Or with Rails
Lease.where("ends_on < CURRENT_DATE interval '1 day' * notice_days")
Or with Arel
Lease.where(Lease.arel_table[:ends_on].lt(Arel.sql("CURRENT_DATE notice_days")))