CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Marc O'Morain
Marc O'Morain

Reputation: 3759

Find ActiveRecord objects created or updated in a time range

I have an ActiveRecord model with the standard created_at, updated_at timestamps. I want to run a query to find all records created or updated in a time range.

It possible to do this in a single ActiveRecord query?

The following works great for a single clause (just querying on created_at, for example).

range = 2.weeks.ago .. 1.week.ago
=> Mon, 03 Jun 2013 14:49:54 UTC +00:00..Mon, 10 Jun 2013 14:49:54 UTC +00:00

Item.where(:created_at => range)
  Item Load (0.4ms)  SELECT `items`.* FROM `items` WHERE (`items`.`created_at` BETWEEN '2013-06-16 14:40:55' AND '2013-06-17 14:40:55')
=> [... results ...]

When I try to add the second clause ActiveRecord is producing an AND statement, which is not what I want:

Item.where(:created_at => range, :updated_at => range)
  SELECT `items`.* FROM `items`
    WHERE (`items`.`created_at` BETWEEN '2013-06-16 14:40:55' AND '2013-06-17 14:40:55')
    AND (`items`.`updated_at` BETWEEN '2013-06-16 14:40:55' AND '2013-06-17 14:40:55')

I could do the query with a single statement and add the results to a set to remove duplicates, but it really feels like something that should be a possible on once query.

And ideas?

Upvotes: 0

Views: 1173

Answers (3)

wintermeyer
wintermeyer

Reputation: 8318

You overcomplicate your code by searching for created_at AND updated_at. You only need to search for updated_at because that gets set at a create too. So the solution for your problem is:

range = 2.weeks.ago .. 1.week.ago
Item.where(:updated_at => range)

Upvotes: 4

Manoj Sehrawat
Manoj Sehrawat

Reputation: 1303

check out gem "squeel" it will definitely help you out. Its really awesome. A railscast on this is also there:

https://github.com/ernie/squeel

http://railscasts.com/episodes/354-squeel?view=asciicast

Upvotes: 0

user229044
user229044

Reputation: 239312

You'll have to write your own statement if you want to OR two conditions together:

Item.where("(created_at BETWEEN ? AND ?) or (updated_at BETWEEN ? and ?)", 2.weeks ago, 1.week ago, 2.weeks_ago, 1.week_ago)

Upvotes: 2

Related Questions