millisami
millisami

Reputation: 10161

group by in mongoid/mongodb

Unit has_many StationInstances

StationInstance belongs_to unit

I want to group station instances by unit_id in mongoid.

What will be the query to get all the station instances grouped by unit_id?

Upvotes: 2

Views: 3047

Answers (1)

RameshVel
RameshVel

Reputation: 65887

You can do this directly using group (which internally a map reduce) method in mongo as you did in sql. the code is

  db.StationInstance.group({key:{unit_id:true},
                            cond:{},
                            reduce:function(a,b){b.sum++;},
                            initial:{sum:0}});

this will return only the counts

>> [ { "unit_id" : xxxx, "sum" : 1 }, { "unit_id" : zzzzz, "sum" : 2 } ]

But you cannot get the StationInstance items along with it

Instead You can do this with MongoDB map-reduce.. check the below code

map = function() { 
    emit(this.unit_id,{count:1,StationInstance:this._id}); 
}

In map function you can group by unit_id of StationInstance (since it belongs to unit, it will have unit_id), and aggregating it by count. since plain count doesn't return the whole object ,we explicitly return the id (or whatever field or whole object by just this ) of the StationInstance

reduce = function(k, values) {
    var result = {count: 0, StationInstance: []};
    values.forEach(function(value) {
        result.count += value.count;
        result.StationInstance.push(value.StationInstance);
    });
    return result;
}

and in reduce we count the grouped item, and put the StationInstances into an array

finally issuing

 db.StationInstance.mapReduce(map,reduce,{out: { inline : 1}})

will return the desired result

to do this in mongoid,just stringfiy the functions map & reduce and use it like

StationInstance.collection.mapreduce(map,reduce,{:out => {:inline => 1},:raw=>true })

Upvotes: 1

Related Questions