CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4postgresql-9.1ruby-2.1
steel
steel

Reputation: 12580

Rails query: Compare calculation of two attribute values

I have a model, Product, which has both a :created_at timestamp and an :expiration_in_days attribute. Products are considered expired a certain number of days after their creation. How do I write a query that only returns products that have not expired?

Rails 4, Ruby 2.1, PG 0.17

I have been trying queries like this, with no success:

#product.rb
def self.not_expired
  where('created_at + expiration_in_days * 86400 >= ?', Time.now)
end

I have a pure Ruby version that works fine, it's just slower:

#product.rb
def self.not_expired
  select{ |p| (p.created_at.to_i + p.expiration_in_days * 86400) >= Time.now.to_i}
end

Any pointers on more advanced Rails queries than the documentation (http://guides.rubyonrails.org/active_record_querying.html) would be very welcome as well.

Thanks in advance.

Upvotes: 0

Views: 601

Answers (3)

wicz
wicz

Reputation: 2313

Try this:

def self.not_expired
  where("created_at + (expiration_in_days * 86400)::text::interval >= ?", Time.now)
end

UPDATE: Add references

You can learn more about date and time function here.

Given your case has a special requirement, which is the value of expiration_in_days is a column in the table, we cannot use created_at + interval expiration_in_days day. Instead, we need to type cast its value to interval. But you can't type cast straight to an integer, that's why we cast it to text first.

Upvotes: 1

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42899

I'm not sure if this would work but try something like this

def self.not_expired
  where("TIMESTAMP created_at + INTERVAL '1 day' * expiration_in_days >= NOW()")
end

Upvotes: 0

Carl
Carl

Reputation: 991

A + B > C is true if A <= C - B

So, instead of trying to add the expiration time to created_at, subtract the expiration time from Time.now.

def expiration_threshold
  Time.now - amount_of_time # I'm not sure what type to use
end

def self.not_expired
  where( "created_at > ? ", expiration_threshold )
end

I've always been a little stumped about what type Rails/Ruby will want when dealing with various dates/times, you may have to play around.

Upvotes: 0

Related Questions