Querying last 30 days date range with Mongoid and Ruby?

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?

Answers


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.


There's a simpler way:

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

Adjust time range to suit.


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

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

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...


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.


Need Your Help

Does every file have a MIME type associated with it?

php security mime file-extension

I recently implemented a security system where we check MIME types and file extensions against a list of acceptable ones. If the scanned file has a MIME and an extension in this list, we move forwa...

Copy a $_FILE array to a $_SESSION array and make the upload of a file using the $_SESSION array

php html file session session-variables

I'm trying to make the upload of a file .pdf in a directory of my server (now I'm using localhost).