2

I have the following Scope:

def coming_up_on_renewal(product_name = "product_name")
  where('billing_start_date = NOW() + INTERVAL 3 day AND status = ? AND product_name = ? AND auth_net_subscription_id IS NOT NULL', :active, "#{product_name}")
end

And I have manipulated a single record in my database to be 3 days in the future, yet when I run this scope, an empty array is returned.

I am basically trying to retrieve records where the billing_start_date is 3 days from the current day.

dennismonsewicz
  • 25,132
  • 33
  • 116
  • 189

1 Answers1

4

Have a try with this.

def coming_up_on_renewal(product_name)
  where(:status => :active, :product_name => product_name)
  .where(:billing_start_date => 
   (3.days.from_now.beginning_of_day)..(3.days.from_now.end_of_day))
  .where('auth_net_subscription_id IS NOT NULL')
end

For your additional "IS NOT NULL" condition see this answer.

Community
  • 1
  • 1
Thomas Klemm
  • 10,678
  • 1
  • 51
  • 54
  • Im getting an error when running your activerecord statement: `syntax error, unexpected ',', expecting tASSOC ...name, 'billing_start_date = ?', 3.days.from_now)` – dennismonsewicz Dec 12 '12 at 16:48
  • Thanks for the help! I was able to get it with the following: `where(:status => :active, :product_name => product_name, :billing_start_date => (Time.now.midnight + 3.day)..(Time.now.end_of_day + 3.day)).where('auth_net_subscription_id IS NOT NULL')` – dennismonsewicz Dec 12 '12 at 16:52
  • 1
    I've edited my answer, though I am not happy with the `billing_start_date` part. With `3.days.from_now` it's looking for records with exactly that timestamp, down to the second. You might look more for a date range. – Thomas Klemm Dec 12 '12 at 16:52
  • I changed my scope to match your latest edit... works perfectly! – dennismonsewicz Dec 12 '12 at 16:57
  • 1
    And now included your feedback. Thanks. – Thomas Klemm Dec 12 '12 at 17:00