3

I am new to MongoDB and I am not sure how to solve in an efficient way this problem. In my app, a user can choose between one object or another (multiple times, by giving positive/negative ratings). What I want to do is to count how many times an object has been rated positive and negative. To give you a more precise idea, here is some data sample (I removed some fields to make it simpler) (I need the data in this form because a user is not allowed to rate the same pair of objects twice):

{
    "_id" : ObjectId("579688a5336f04d208747fe8"),
    "positive" : ObjectId("5796708d58cc25ab040a6cda"),
    "negative" : ObjectId("5796705c58cc25ab040a6cd7")
},
{
    "_id" : ObjectId("579688a7336f04d208747fe9"),
    "positive" : ObjectId("5796706358cc25ab040a6cd8"),
    "negative" : ObjectId("5796708058cc25ab040a6cd9")
},
{
    "_id" : ObjectId("579688a8336f04d208747fea"),
    "positive" : ObjectId("5796708d58cc25ab040a6cda"),
    "negative" : ObjectId("5796706358cc25ab040a6cd8")
},
{
    "_id" : ObjectId("579688a8336f04d208747feb"),
    "positive" : ObjectId("5796708d58cc25ab040a6cda"),
    "negative" : ObjectId("5796706358cc25ab040a6cd8")
}...

I need to run a command which returns data in this way:

{
  "_id": ObjectId("5796708d58cc25ab040a6cda"),
  "positiveCount": 3,
  "negativeCount": 0
},
{
  "_id": ObjectId("5796705c58cc25ab040a6cd7"),
  "positiveCount": 0,
  "negativeCount": 1
}, ...

So far I am able to count just the number of positive or negative ratings separately using this command:

db.getCollection('votes').aggregate([
{$group:{_id:'$positive', count: {$sum:1}}}
]);

This works but only to count either positive or negative votes. This is the result I get

{
    "_id" : ObjectId("57971bb60711a9a6046dccc7"),
    "count" : 7.0
},
{
    "_id" : ObjectId("5796708058cc25ab040a6cd9"),
    "count" : 21.0
}...

My solution so far is to run the query twice (one for positive and one for negative ratings) and then merge them in the app level. I am asking you if there is a better and more performant way to achieve this result. Thanks in advice.

Neil Lunn
  • 130,590
  • 33
  • 275
  • 280
psandro
  • 43
  • 5

1 Answers1

0

I suggest you to use a mapReduce function. Here is a possible solution :

var map = function() {
    if (this.positive) {
        emit(this.positive, { positiveCount: 1, negativeCount: 0 });
    }
    if (this.negative) {
        emit(this.negative, { positiveCount: 0, negativeCount: 1 });
    }
};

var reduce = function (key, values) {
     var o = { positiveCount: 0, negativeCount: 0 };
     for (var i = 0; i < values.length; i++) {
         if (values[i].positiveCount === 1) {
             o.positiveCount++;
         }
         if (values[i].negativeCount === -1) {
             o.negativeCount++;
         }
     }
     return o;
};

db.pairs.mapReduce(map, reduce, { out: "solution" } );

Now, you have the total count positive/negative. Query the solution collection and you can see results.

Odonno
  • 379
  • 2
  • 5
  • Thanks for you answer. I've already thought about this solution but it doesn't fit my requirements. I need to run a query because I'll had some filters too, like "get positive/negative votes for ratings made after a specific date". To make it more clear: a user cannot vote twice for a specific pair of objects, but he can still vote the same object multiple times (against different objects). I hope it's a bit more clear now – psandro Jul 27 '16 at 12:02
  • Not really no. I mean, what solution is close to your needs ? Why it does not fit, what is missing ? – Odonno Jul 27 '16 at 12:33
  • In my question I posted a partial solution: it is a query which counts how many times a document has been rated positive but I need the number of negative ratings too (for the same document). I also posted the expected result that I need. – psandro Jul 27 '16 at 18:57
  • Oh, yeah, sorry. I think a mapReduce function should do the trick. I'll edit my answer. – Odonno Jul 27 '16 at 19:09
  • Thank you so much! Your answer really helped me. There was a little issue: when the emit function emits only a single value for a specific Id, then the reduce function will not be called. Therefore I had to add a finalize function to manage this case. I uploaded the final version using mongoose here: [pastebin](http://pastebin.com/5dyYnCd4) – psandro Jul 28 '16 at 09:25
  • Ah, that it is maybe why mongodb recommend to send the send object format on both `map` and `reduce` function. I don't think the `finalize` function is necessary. I updated my answer. Is it better ? – Odonno Jul 28 '16 at 10:58