Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I search date range in Rails with variable date

How can I do this in rails active record¿?

find all models that match (created_at + 100 days between this month)

Edit: Ok, Sorry for not be precise this what I'm trying to do in active record in Rails 3.0 way:

select 
    distinct p.ID 
from
    patients p
    inner join vaccines_patients vp on p.ID = vp.patient_id
    inner join vaccines v on v.ID = vp.VACCINE_ID
where
    month(vp.appliedAt + INTERVAL v.duration DAY) = month(now())

I want to get a similar query but using where in active record.

like image 863
Giancarlo Corzo Avatar asked Mar 12 '11 19:03

Giancarlo Corzo


4 Answers

ActiveRecord can build a query using a BETWEEN when it accepts a Range. This sounds like it might be more what you are looking for.

YourModel.where(created_at: 100.days.ago..100.days.from_now)

It always seems a little simpler to do this than using >= <= in the query

like image 185
Matt Polito Avatar answered Nov 12 '22 13:11

Matt Polito


In Rails 3 you can use:

YourModel.where(:created_at => start_date..end_date)

where start_date and end_date are Date class.

like image 26
Cristhian Boujon Avatar answered Nov 12 '22 13:11

Cristhian Boujon


You didn't specify Rails 2 or 3, and I'm not entirely sure what range you're actually looking for, but this should get you started. Please add some example dates and say whether they should fall into your range or not.

In Rails 2 you can use a named_scope in your model.

# This range represents "created_at" values that are within 100 days on either side of today.
# Please clarify what "created_at + 100 days between this month" means if you need help
# refining this.
#
named_scope :within_range, lambda {{ :conditions => ["created_at <= ? AND created_at >= ?", Date.today + 100, Date.today - 100] }}

In Rails 3, you would use a scope with the new Arel scope methods:

scope :within_range, lambda { where("created_at <= ? AND created_at >= ?", Date.today + 100, Date.today - 100) }
like image 9
jdl Avatar answered Nov 12 '22 15:11

jdl


If I understood, you need something like this:

date = Date.today
after = date.start_of_month - 100
before = date.end_of_month - 100

YourModel.find(:all, :conditions => ['created_at > ? AND created_at < ?', after, before])

or in scope (rails 2):

# shorter name is needed
named_scope :created_100_days_before_this_month, lambda do |date|
  after = date.start_of_month - 100
  before = date.end_of_month - 100
  YourModel.find(:all, :conditions => ['created_at > ? AND created_at < ?', after, before])
end
like image 1
santuxus Avatar answered Nov 12 '22 13:11

santuxus