Home > front end >  How do I reference the value of a different attribute in a Rails ActiveRecord Query
How do I reference the value of a different attribute in a Rails ActiveRecord Query

Time:06-13

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")))
  • Related