Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mongodb - Group by values and conditions

Very new to mongo and having a little trouble getting my head around this problem. I have collection looking like this

{ "_id" : "PN89dNYYkBBmab3uH", "card_id" : 1, "vote" : 1, "time" : 1437700845154 }
{ "_id" : "Ldz7N5syeW2SXtQzP", "card_id" : 1, "vote" : 1, "time" : 1437700846035 }
{ "_id" : "v3XWHHvFSHwYxxk6H", "card_id" : 1, "vote" : 2, "time" : 1437700849817 }
{ "_id" : "eehcDaCyTdz6Yd2a9", "card_id" : 2, "vote" : 1, "time" : 1437700850666 }
{ "_id" : "efhcDaCyTdz6Yd2b9", "card_id" : 2, "vote" : 1, "time" : 1437700850666 }
{ "_id" : "efhcDaCyTdz7Yd2b9", "card_id" : 3, "vote" : 1, "time" : 1437700850666 }
{ "_id" : "w3XWgHvFSHwYxxk6H", "card_id" : 1, "vote" : 1, "time" : 1437700849817 }

I need to write a query that will group cards basically by votes (1 is like; 2 is dislike). So I need to get the total number of likes minus dislikes for each card and be able to rank them.

Result would be like:

card_id 1: 3 likes - 1 dislike = 2

card_id 3: 1 like - 0 dislikes = 1

card_id 2: 2 likes - 0 dislikes = 0

Any idea how to get the count of all the cards' 1 votes minus 2 votes and then order the results?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

To do any sort of "grouping" with MongoDB queries then you want to be able to use the aggregation framework or mapReduce. The aggregation framework is generally preferred as it uses native coded operators rather than JavaScript translation, and is therefore typically faster.

Aggregation statements can only be run on the server API side, which does make sense because you would not want to do this on the client. But it can be done there and make the results available to the client.

With attribution to this answer for providing the methods to publish results:

Meteor.publish("cardLikesDislikes", function(args) {
    var sub = this;

    var db = MongoInternals.defaultRemoteCollectionDriver().mongo.db;

    var pipeline = [
        { "$group": {
            "_id": "$card_id",
            "likes": {
                "$sum": {
                    "$cond": [
                        { "$eq": [ "$vote", 1 ] },
                        1,
                        0
                    ]
                }
            },
            "dislikes": {
                "$sum": {
                    "$cond": [
                        { "$eq": [ "$vote", 2 ] },
                        1,
                        0
                    ]
                }
            },
            "total": {
                "$sum": {
                    "$cond": [
                        { "$eq": [ "$vote", 1 ] },
                        1,
                        -1
                    ]
                }
            }
        }},
        { "$sort": { "total": -1 } }
    ];

    db.collection("server_collection_name").aggregate(        
        pipeline,
        // Need to wrap the callback so it gets called in a Fiber.
        Meteor.bindEnvironment(
            function(err, result) {
                // Add each of the results to the subscription.
                _.each(result, function(e) {
                    // Generate a random disposable id for aggregated documents
                    sub.added("client_collection_name", Random.id(), {
                        card: e._id,                        
                        likes: e.likes,
                        dislikes: e.dislikes,
                        total: e.total
                    });
                });
                sub.ready();
            },
            function(error) {
                Meteor._debug( "Error doing aggregation: " + error);
            }
        )
    );

});

The general aggregation statement there is just a $group operation on the single key of "card_id". In order to get the "likes" and "dislikes" you use a "conditional expression" which is $cond.

This is a "ternary" operator which considers a logical test on the valueof "vote", and where it matches the expect type then a positive 1 is returned, otherwise it is 0.

Those values are then sent to the accumulator which is $sum to add them together, and produce the total counts for each "card_id" by either "like" or "dislike".

For the "total", the most efficient way is to attribute a "positive" value for "like" and a negative value for "dislike" at the same time as doing the grouping. There is an $add operator, but in this case it's usage would require another pipeline stage. So we just do it on a single stage instead.

At the end of this there is a $sort in "descending" order so the largest positive vote counts are on top. This is optional and you might just want to use dynamic sorting client side. But it is a good start for a default that removes the overhead of having to do that.

So that is doing a conditional aggregation and working with the results.


Test listing

This is what I tested with the a newly created meteor project, with no addins and just a single template and javascript file

console commands

meteor create cardtest
cd cardtest
meteor remove autopublish

Created the "cards" collection in the database with the documents posted in the question. And then edited the default files with the contents below:

cardtest.js

Cards = new Meteor.Collection("cardStore");

if (Meteor.isClient) {

  Meteor.subscribe("cards");

  Template.body.helpers({
    cards: function() {
      return Cards.find({});
    }
  });

}

if (Meteor.isServer) {

  Meteor.publish("cards",function(args) {
    var sub = this;

    var db = MongoInternals.defaultRemoteCollectionDriver().mongo.db;

    var pipeline = [
      { "$group": {
        "_id": "$card_id",
        "likes": { "$sum": { "$cond": [{ "$eq": [ "$vote", 1 ] },1,0] } },
        "dislikes": { "$sum": { "$cond": [{ "$eq": [ "$vote", 2 ] },1,0] } },
        "total": { "$sum": { "$cond": [{ "$eq": [ "$vote", 1 ] },1,-1] } }
      }},
      { "$sort": { "total": -1, "_id": 1 } }

    ];

    db.collection("cards").aggregate(
      pipeline,
      Meteor.bindEnvironment(
        function(err,result) {
          _.each(result,function(e) {
            e.card_id = e._id;
            delete e._id;

            sub.added("cardStore",Random.id(), e);
          });
          sub.ready();
        },
        function(error) {
          Meteor._debug( "error running: " + error);
        }
      )
    );

  });
}

cardtest.html

<head>
  <title>cardtest</title>
</head>

<body>
  <h1>Card aggregation</h1>

  <table border="1">
    <tr>
      <th>Card_id</th>
      <th>Likes</th>
      <th>Dislikes</th>
      <th>Total</th>
    </tr>
    {{#each cards}}
      {{> card }}
    {{/each}}
  </table>

</body>

<template name="card">
  <tr>
    <td>{{card_id}}</td>
    <td>{{likes}}</td>
    <td>{{dislikes}}</td>
    <td>{{total}}</td>
  </tr>
</template>

Final aggregated collection content:

[
   {
     "_id":"Z9cg2p2vQExmCRLoM",
     "likes":3,
     "dislikes":1,
     "total":2,
     "card_id":1
   },
   {
     "_id":"KQWCS8pHHYEbiwzBA",
      "likes":2,
      "dislikes":0,
      "total":2,
      "card_id":2
   },
   {
      "_id":"KbGnfh3Lqcmjow3WN",
      "likes":1,
      "dislikes":0,
      "total":1,
      "card_id":3
   }
]

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...