按值和条件分组 [英] Group by values and conditions

问题描述

对于mongo来说是一个新手，在解决这个问题时遇到了一些麻烦.我有这样的收藏

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 }

我需要编写一个查询，该查询将基本上按票对卡进行分组(1表示喜欢； 2表示不喜欢).因此，我需要获得每张卡的喜欢总数减去喜欢的总数，然后才能对它们进行排名.

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.

结果将是:

card_id 1:3个赞-1个不喜欢= 2

card_id 1: 3 likes - 1 dislike = 2

card_id 3:1喜欢-0不喜欢= 1

card_id 3: 1 like - 0 dislikes = 1

card_id 2:喜欢2次-不喜欢0次= 0

card_id 2: 2 likes - 0 dislikes = 0

有什么主意如何获得所有卡的1票减去2票的票数，然后对结果进行排序?

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

推荐答案

要对MongoDB查询进行任何形式的分组"，那么您希望能够使用

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.

聚合语句只能在服务器API端运行，这很有意义，因为您不想在客户端上执行此操作.但这可以在那里完成，并将结果提供给客户.

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);
            }
        )
    );

});

一般的聚合语句只有一个 $group 对"card_id"的单个键进行操作.为了获得喜欢"和不喜欢"，请使用条件表达式"，该条件表达式为

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.

这是一个三元"运算符，它考虑对"vote"的值进行逻辑测试，如果与期望类型匹配，则返回正数1，否则返回0.

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.

然后将这些值发送到 $sum 将它们加在一起，然后按喜欢"或不喜欢"产生每个"card_id"的总数.

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".

对于总计"，最有效的方法是在进行分组的同时为喜欢"赋予正"值，为不喜欢"赋予负值.有一个 $add 运算符，但在这种情况下，它的使用将需要另一个管道阶段.因此，我们只是在一个阶段中完成它.

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.

最后，有一个 $sort 以降序"排列，因此最大的赞成票数居首.这是可选的，您可能只想使用动态排序客户端.但这是默认设置的一个很好的开始，它消除了必须这样做的开销.

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.

这是我在一个新创建的流星项目中进行的测试，该项目没有插件，只有一个模板和javascript文件

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

控制台命令

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>

最终汇总集合内容:

[
   {
     "_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
   }
]

这篇关于按值和条件分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！