问题描述:

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.

网友答案:

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) }
网友答案:

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:

  • PostgreSQL how to concat interval value '2 days'
  • Using a variable period in an interval in Postgres
  • http://www.postgresql.org/message-id/[email protected]

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

Subscription.where("CAST(created_at AS DATE) = (CURRENT_DATE - (subscriptions.duration || ' ' || subscriptions.interval)::interval )")
相关阅读:
Top