Rails 3 query in multiple date ranges

active-relation, activerecord, ruby-on-rails-3

Solution

It gets a little aggressive with paren, but prepare to dive down the arel rabbit hole

ranges = [
          ((12.months.ago)..(8.months.ago)),
          ((7.months.ago)..(6.months.ago)),
          ((5.months.ago)..(4.months.ago)),
          ((3.months.ago)..(2.months.ago)),
          ((1.month.ago)..(15.days.ago))
         ]
table = Post.arel_table
query = ranges.inject(table) do |sum, range|
  condition = table[:created_at].in(range)
  sum.class == Arel::Table ? condition : sum.or(condition)
end

Then, query.to_sql should equal

(((("sessions"."created_at" BETWEEN '2011-06-05 12:23:32.442238' AND '2011-10-05 12:23:32.442575' OR "sessions"."created_at" BETWEEN '2011-11-05 12:23:32.442772' AND '2011-12-05 12:23:32.442926') OR "sessions"."created_at" BETWEEN '2012-01-05 12:23:32.443112' AND '2012-02-05 12:23:32.443266') OR "sessions"."created_at" BETWEEN '2012-03-05 12:23:32.443449' AND '2012-04-05 12:23:32.443598') OR "sessions"."created_at" BETWEEN '2012-05-05 12:23:32.443783' AND '2012-05-21 12:23:32.443938')

And you should be able to just do Post.where(query)

EDIT

You could also do something like:

range_conditions = ranges.map{|r| table[:created_at].in(r)}
query = range_conditions.inject(range_conditions.shift, &:or)

to keep it a little more terse

Problem

Suppose we have some date ranges, for example: ``` ranges = [ [(12.months.ago)..(8.months.ago)], [(7.months.ago)..(6.months.ago)], [(5.months.ago)..(4.months.ago)], [(3.months.ago)..(2.months.ago)], [(1.month.ago)..(15.days.ago)] ] ``` and a `Post` model with `:created_at` attribute. I want to find posts where `created_at` value is in this range, so the goal is to create a query like: ``` SELECT * FROM posts WHERE created_at BETWEEN '2011-04-06' AND '2011-08-06' OR BETWEEN '2011-09-06' AND '2011-10-06' OR BETWEEN '2011-11-06' AND '2011-12-06' OR BETWEEN '2012-01-06' AND '2012-02-06' OR BETWEEN '2012-02-06' AND '2012-03-23'; ``` If you have only one range like this: ``` range = (12.months.ago)..(8.months.ago) ``` we can do this query: ``` Post.where(:created_at => range) ``` and query should be: ``` SELECT * FROM posts WHERE created_at BETWEEN '2011-04-06' AND '2011-08-06'; ``` Is there a way to make this query using a notation like this `Post.where(:created_at => range)`? And what is the correct way to build this query? Thank you

Original source