MongoDB Group by / Map reduce

Question

Hey guys I have an existing messaging schema in mongoDB which works well.

{       
    "_id" : ObjectId("4f596b4543658618c0000004"),
    "user_id" : ObjectId("4f4c6c5143658618dc000002"),
    "body" : "message body",
    "from_user" : {
        "_id" : ObjectId("4f4c6b6943658618dc000001"),
        "name" : "Mister Quin"
    },
    "created_at" : ISODate("2012-03-09T02:30:29Z")
}

Now I want to display a list of people a given user has messaged. You can think of it as a message inbox that combines messages I am the sender and recipient of denoted by "user_id", and "from_user._id" respectively. So in essence group unique messages between two parties from the message collection. Any help I can get would be appreciated. I know it's probably a map reduce problem.

I am using mongoid as my ORM but that shouldn't matter much here.

Thanks.

Kyle Banker · Accepted Answer · 2012-03-09 16:35:12Z

1

You can use group and group by from_user._id and user_id.

db.messages.group({key: {'from_user._id': 1, user_id: 1},
                   initial: {sum: 0}, 
                   reduce: function(doc, prev) {prev.sum += 1},
                   cond: {from_user._id: ObjectId("4f4c6b6943658618dc000001")})

That will return a list of all users messaged by Mister Quin, and the number of times each was messaged. Make sure you have an index on "from_user._id"

answered Mar 9, 2012 at 16:35

Kyle Banker

4,36926 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Emmanuel P Over a year ago

This will work in 1 direction (all messages sent by mister Quin) but it will not get the messages sent to Mister Quin, (specified by the "user_id" key. If I change the condition to an $or it does pick up incoming messages, but doesn't group properly.

db.messages.group({key: {user_id: 1, 'from_user._id': 1},                    initial: {sum: 0},                     reduce: function(doc, prev) {prev.sum += 1},                    cond: {$or : [{'from_user._id': ObjectId("4f4c6b6943658618dc000001")}, {user_id: ObjectId("4f4c6b6943658618dc000001")}]}} 				)

This returns a duplicate entry

Kyle Banker Over a year ago

If you want all the messages sent to Mr Quin, then change the cond to {user_id: ObjectId("4f4c6b6943658618dc000001")}. You'll be running two separate group queries to get both sides of the relation.

Emmanuel P Over a year ago

Yeah that's the issue, I didn't want it in two queries since I want to list all messages ordered by most recent. If I did it in 2 queries I'd need to do the sorting on the application level. But I suppose that could work for now. Thanks a lot for your help.

Collectives™ on Stack Overflow

MongoDB Group by / Map reduce

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related