Rails: make this query database agnostic ...?
I have these two lines in my model, written for PostgreSQL:
named_scope :by_month, lambda { |month| { :conditions => ["EXTRACT(MONTH FROM recorded_on) = ?", month] }}
named_scope :by_year, lambda { |year| { :conditions => ["EXTRACT(YEAR FROM recorded_on) = ?", year] }}
I am running PostgreSQL in production, but I am developing with SQLite3. How can I write these lines in such a way that it is a database aggregation?
Btw, "registered_on" is formed from the following:
Model.recorded_on = Time.parse("Fri, 01 May 2009 08:42:23 -0400")
+1
a source to share
2 answers
Ok, figured out what's the best way (thanks for this article ):
Basically, do nothing in the model!
date = Date.new(year, month, 1)
Model.find(:all, :conditions => { :recorded_on => date..date.end_of_month })
+3
a source to share
BETWEEN should be pretty generic: how about the following:
named_scope :by_month, lambda { |month|
d1 = Date.new(2009, month, 1)
d2 = d1.end_of_month
{ :conditions => ['recorded_on between ? and ?', d1, d2]}
}
named_scope :by_year, lambda { |year|
d1 = Date.new(year, 1, 1)
d2 = d1.end_of_year
{ :conditions => ['recorded_on between ? and ?', d1, d2]}
}
If you have time, you need a little smarter with hours, minutes and seconds.
0
a source to share