Querying last 30 days date range with Mongoid and Ruby?

Question

How do I go about querying a date range (of say the last 30 days from now) with Mongoid and Ruby?

I need to end up with an array or hash like the following:

{
    15 => 300,
    14 => 23,
    13 => 23
    ...
    30 => 20  # Goes over into previous month
    28 => 2
}

I am currently storing each document with a DateTime instance as well as a unix timestamp Integer field.

The keys in the above hash are the days and the values are the sum of all sales for those days.

Any ideas?

score 34 · Answer 1 · edited Jul 11 '14 at 09:36

34

There's a simpler way:

Sale.where(created_at: (30.days.ago..Time.now))

Adjust time range to suit.

edited Jul 11 '14 at 09:36

Chris Seymour

83,387
30
160
202

answered Jun 16 '12 at 14:33

tomblomfield

889
2
9
11

This requires ActiveSupport for the .days.ago portion – Dan Healy Mar 29 '13 at 19:06
I don't think you can add multiple params with the hash syntax this way. Might be wrong though. – Ash Blue Aug 06 '13 at 20:04
@AshBlue - it's a single argument, of type Range. http://ruby-doc.org/core-2.0.0/Range.html – tomblomfield Sep 16 '13 at 22:30
`Range` in ruby is very **costly**. Although nice syntax, I'd choose do the `gte` and `lte` – oma Sep 17 '13 at 16:20
@oma - could you give some info/link why ruby ranges are costly, especially in that context (mongodb query)? – mfittko Mar 04 '15 at 13:32
@oma Range in ruby is not costly at all. It's converting a range into an array that's very costly, but ORMs should be able to take a range and convert it into either a "BETWEEN ... AND" (sql) or a pair ($lte, $gte) (mongodb). – rewritten Apr 19 '15 at 18:09
In Mongoid, this does create a selector: `selector: {"created_at"=>{"$gte"=>2016-11-25 15:43:56 UTC, "$lte"=>2016-12-25 15:43:56 UTC}}` – B Seven Dec 25 '16 at 15:44
@tomblomfield - In Mongoid 5.1.3, this is emitting an array of Sales. Am I missing something? – B Seven Dec 25 '16 at 15:46

score 26 · Accepted Answer · answered Oct 15 '11 at 20:54

26

Here's how to do it all in rubyland:

sales_by_date = Hash.new(0)

Sale.where(:created_at.gte => (Date.today - 30)).order_by(:created_at, :desc).each do |s|
  sales_by_date[s.created_at.strftime("%m-%d")] += 1
end

This will create a hash with "month-day" keys, reason is that some months have fewer than 30 days and will result in a key collision if the query is always 30.

If you want a different range, change the query:

# Between 10 and 20 days ago
start_day       = 10
end_day         = 20

Sale.where(:created_at.gte => (Date.today - end_day), :created_at.lte => (Date.today - start_day))

Change created_at to whatever the name of your datetime field is.

answered Oct 15 '11 at 20:54

Dan Healy

747
7
13

If use `2012-06-16` instead of `(Date.today - 30)` then how write query? – harsh4u Feb 07 '13 at 10:31
@harsh4u: Convert the string to a Date object first. `Date.parse("2012-06-16")` – Dan Healy Feb 07 '13 at 17:13
this is far from perfect solution, the answers below are way better. – jturolla Dec 19 '14 at 17:39
Sale.where(:end_date.gte => (Date.today), :begin_date.lte => (Date.today)).order(begin_date: :desc) – Kiry Meas Oct 16 '17 at 15:47

score 6 · Answer 3 · answered Sep 03 '14 at 12:13

6

You can also write the query by using the between method like:

Sale.between(created_at: (30.days.ago..Time.now))

answered Sep 03 '14 at 12:13

alup

2,961
1
21
12

score 2 · Answer 4 · answered Jun 04 '14 at 19:00

What if you forgot to put timestamps in your model? :(

No problem! Just use the timestamp in the BSON:ObjectId

Get Sales in the last 30 days.

Sale.where(:id.gte => Moped::BSON::ObjectId.from_time((Date.today - 30).to_time))

Since the id field is index, this may very well be the fastest query.

Need a date range? Cake.

Get Sales from last month.

Sale.and(
  {:id.gte => Moped::BSON::ObjectId.from_time((Date.today.prev_month.beginning_of_month).to_time)},
  {:id.lte => Moped::BSON::ObjectId.from_time((Date.today.prev_month.end_of_month).to_time)}
)

Of course this example assumes Rails date helpers...

I think this is more better answer. It is more difficult but it have nice performance. Thank you! — ryush00, Jul 23 '15 at 16:41

score 0 · Answer 5 · answered Oct 15 '11 at 16:40

You can achieve this for example by doing a map_reduce call over your collection with a map function only emitting the relevant entries (the ones whose date value is greater than whatever condition you give it).

Try something like this:

map = "function () { emit( {this.date.getDate(), {}} )}"
reduce = "function (key, values) { return {date: key, count: values.length } }"

collection.map_reduce(map, reduce, {query: {"date": {"$gt": 30.days.ago } } })

That might work.

Querying last 30 days date range with Mongoid and Ruby?

5 Answers5