CODEWITHSUNDEEP
ruby-on-railsactiverecord
Scott
Scott

Reputation: 694

How do I reference the value of a different attribute in a Rails ActiveRecord Query

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?

Upvotes: 0

Views: 49

Answers (1)

Yurii Stefaniuk
Yurii Stefaniuk

Reputation: 1797

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

Upvotes: 1

Related Questions