Blog
what did i learn today
[mongoid] doing a group-by on date

Doing a simple group-by query using mongo and mongoid is actually pretty straightforward. According to the documentation, mongoid does not offer any group/aggregation function itself, but mongo does. And you can directly access the mongo using the collection. So assume we have a mongo collection: [ruby] class Log include Mongoid::Document include Mongoid::Timestamps::Created field :action, :type => String end [/ruby] and now I want to count all occurrences of the different actions. [ruby] Log.collection.group(:key => "action", :initial => { :count => 0 }, :reduce => "function(doc,prev) { prev.count += +1; }") [/ruby] This will return an array of hashes as follows: [ruby] [{"action"=>"create", "count"=>1565.0}, {"action"=>"update", "count"=>2142.0}, {"action"=>"destroy", "count"=>27.0}] [/ruby] That is already very nice. But now I want to get the results of a certain action, grouped per day and month. [ruby] Log.collection.group(:keyf => "function(doc) { d = new Date(doc.created_at); return {nr_month: d.getMonth(), nr_day: d.getDate() }; }", :initial => { :visits => 0 }, :reduce => "function(doc,prev) { prev.visits += +1; }" ) [/ruby]Notice: the value of :keyf and :reduce is a string containing javascript. This is very flexible, but important: no ruby! But since it is a string, you can use string interpolation to get values in there. We should, of course, add a condition, to limit the result-set. So something like this: [ruby] Log.collection.group(:keyf => "function(doc) { d = new Date(doc.created_at); return {nr_month: d.getMonth() }; }", :initial => { :visits => 0 }, :reduce => "function(doc,prev) { prev.visits += +1; }", :cond => {:action => 'create'}) [/ruby] This will returns the logged create per month.Notice: the :cond and :initial contain regular ruby hashes. Selecting on a date-range is also pretty easy, once you know how this took me hours to find : [ruby] Log.collection.group(:keyf => "function(doc) { d = new Date(doc.created_at); return {nr_month: d.getMonth() }; }", :initial => { :visits => 0 }, :reduce => "function(doc,prev) { prev.visits += +1; }", :cond => {:created_at => {'$gte' => Time.utc(2011,04), '$lt' => Time.utc(2011,05) }) [/ruby] I still have some weird offset error with the dates, since I also get a few from the previous month, but I guess this has something to do with the time-zones. Now, Mongoid can help us to write the condition. We can do something like: [ruby] conditions = Log.where(:created_at.gte => Date.today.at_beginning_of_month, :created_at.lte => Date.today.at_end_of_month).selector Log.collection.group(:keyf => "function(doc) { d = new Date(doc.created_at); return {nr_month: d.getMonth() }; }", :initial => { :visits => 0 }, :reduce => "function(doc,prev) { prev.visits += +1; }", :cond => conditions) [/ruby] Hope this helps.