Rails - check column with another column of same table

Question

In my Rails(ActiveRecord) application, I have a table subscription with 3 column created_at,duration,interval
Given an example

| created_at  | duration |  interval  |  
| 22-5-2013   |    1     |  month     |

I wanna select record which created_at equal to (Date.today - duration.interval) (here Date.today - 1.month)
How can I do this with where clause? do I need to use join same table? I don't have any clue.

Do you want to do it as a validation before saving the record? Why a where clause? — cortex, May 22 '13 at 15:38
@cortex I wanna select records which created_at is 1 month ago.(or 3 years ago or 2 weeks ago).interval can be week,month or year. — shajin, May 22 '13 at 15:49

score 2 · Accepted Answer · answered May 22 '13 at 15:49

2

Just a guess here, but are you looking for subscriptions that have expired? If so I would suggest instead of doing what you're planning, change your application to store the expiry date from the outset. You can easily write a migration to add an expiry date column and fill it in now, then you can add a really simple scope to check if a subscription has expired.

Migrate:

add_column :subscriptions, :expiry_date, :datetime

Subscription.reset_column_information

Subscription.all.each do |subscription|
  duration = 0

  switch subscription.interval
    case 'year'
      duration = subscription.duration * 365
    case 'month'
      duration = subscription.duration * 30
    case 'week'
      duration = subscription.duration * 7     
  end

  subscription.update_attribute(:expiry_date, subscription.created_at + duration.days)
end

Model:

scope :expired, lambda{ where("expiry_date <= ?", Time.now) }

answered May 22 '13 at 15:49

Matt

13,948
6
44
68

I appreciate this and I am gonna use this method.But to make me happy, Is there any way to do this in my way? – shajin May 22 '13 at 15:57
1

I'm very glad I could help! @MrYoshiji has given a suggestion below for doing it with SQL. – Matt May 22 '13 at 15:59
My solution works on PostGres, I don't know about MySQL. Also, the lambda is not required here (lambda expects an argument): `scope :expired, where("expiry_date <= ?", Time.now)` is enough, OR a better way with your lambda: `scope :expired, lambda{ |date| where("expiry_date <= ?", date.try(:to_date) || Date.today) }` and then usable with `Subscription.expired` or `Subscription.expired('2012-12-12')` to get the expired subscriptions at either today or another date – MrYoshiji May 22 '13 at 16:05
3

Hi @MrYoshiji, the lambda is required to make the scope reevaluate. If you leave it off then the code is evaluated once and the Time.now stays at that value in the cache. - A longer explanation of this can be found here: http://stackoverflow.com/questions/5257452/rails-default-scoping-being-cached-by-query-cache – Matt May 22 '13 at 16:09

score 1 · Answer 2 · edited May 23 '17 at 12:18

Maybe this can work for you (worked with PostGre SQL):

Subscription.where("CAST(created_at AS DATE) = (CURRENT_DATE - interval '1 month')")

I can't get it to work with columns, but maybe this can give you the beginning of it:

Subscription
  .select("subscriptions.*, subscriptions.duration || ' ' || subscriptions.interval AS my_interval")
  .where("CAST(created_at AS DATE) = (CURRENT_DATE + interval my_interval)")

And then there is plenty of posts about having a variable inside the interval function:

Ah! Got it to work (on PostGre SQL):

Subscription.where("CAST(created_at AS DATE) = (CURRENT_DATE - (subscriptions.duration || ' ' || subscriptions.interval)::interval )")

There is probably the equivalent in MySQL, but @Matt 's answer is a good way for the same purpose. — MrYoshiji, May 22 '13 at 16:07

Rails - check column with another column of same table

2 Answers2

Ah! Got it to work (on PostGre SQL):