php - 按字段分组在 MongoDB 中不起作用

Question

我正在尝试计算数据库中所有记录的总和，我需要避免重复。我编写了这段代码来对记录进行分组，但它对我不起作用。

$pipeline = [
    ['$match' =>
            $criteria->getCondition()],
    ['$group' => 
            ['_id' => '$order_id', 'total' => ['$sum' => '$'.$column]]]
];

$this->getDbConnection()->aggregate('ticket_cache', $pipeline);

测试请求:

db.getCollection('ticket_cache').aggregate(
{
"$match":
    {"event_id":64}
},
{
    "$group" : 
        {"_id":"$order_id", "total": {"$sum":"$payment_amount"}}
})

结果:

/* 1 */
{
    "result" : [ 
        {
            "_id" : NumberLong(7002),
            "total" : 9000.0000000000000000
        }
    ],
    "ok" : 1.0000000000000000
}

数据库中的数据:

/* 1 */
{
    "result" : [ 
        {
            "_id" : ObjectId("553f8b4fbfabe2772f8b4f51"),
            "event_id" : NumberLong(64),
            "ticket_id" : NumberLong(8563),
            "ticket_code" : NumberLong(22062299),
            "ticket_type_id" : NumberLong(391),
            "ticket_created" : NumberLong(1430227620),
            "ticket_deleted" : NumberLong(0),
            "ticket_user_id" : NumberLong(2),
            "ticket_used" : NumberLong(0),
            "order_id" : NumberLong(7002),
            "order_code" : NumberLong(517005),
            "order_created" : NumberLong(1430227620),
            "order_deleted" : NumberLong(0),
            "order_sales_pipeline" : NumberLong(18),
            "order_invoice_id" : NumberLong(4202),
            "order_invoice_amount" : 3000.0000000000000000,
            "order_invoice_created" : NumberLong(1430227641),
            "order_invoice_deleted" : NumberLong(0),
            "order_invoice_code" : NumberLong(420155),
            "payment_id" : NumberLong(4365),
            "payment_amount" : 3000.0000000000000000,
            "payment_currency" : NumberLong(4),
            "payment_author_id" : NumberLong(1),
            "payment_type_id" : NumberLong(27),
            "payment_created" : NumberLong(1430227641),
            "payment_deleted" : NumberLong(0),
            "create_time" : ISODate("2015-04-28T13:29:51.328Z")
        }, 
        {
            "_id" : ObjectId("553f8b4fbfabe2772f8b4f4f"),
            "event_id" : NumberLong(64),
            "ticket_id" : NumberLong(8561),
            "ticket_code" : NumberLong(49287433),
            "ticket_type_id" : NumberLong(391),
            "ticket_created" : NumberLong(1430227620),
            "ticket_deleted" : NumberLong(0),
            "ticket_user_id" : NumberLong(2),
            "ticket_used" : NumberLong(0),
            "order_id" : NumberLong(7002),
            "order_code" : NumberLong(517005),
            "order_created" : NumberLong(1430227620),
            "order_deleted" : NumberLong(0),
            "order_sales_pipeline" : NumberLong(18),
            "order_invoice_id" : NumberLong(4202),
            "order_invoice_amount" : 3000.0000000000000000,
            "order_invoice_created" : NumberLong(1430227641),
            "order_invoice_deleted" : NumberLong(0),
            "order_invoice_code" : NumberLong(420155),
            "payment_id" : NumberLong(4365),
            "payment_amount" : 3000.0000000000000000,
            "payment_currency" : NumberLong(4),
            "payment_author_id" : NumberLong(1),
            "payment_type_id" : NumberLong(27),
            "payment_created" : NumberLong(1430227641),
            "payment_deleted" : NumberLong(0),
            "create_time" : ISODate("2015-04-28T13:29:51.316Z")
        }, 
        {
            "_id" : ObjectId("553f8b4fbfabe2772f8b4f50"),
            "event_id" : NumberLong(64),
            "ticket_id" : NumberLong(8562),
            "ticket_code" : NumberLong(24016753),
            "ticket_type_id" : NumberLong(391),
            "ticket_created" : NumberLong(1430227620),
            "ticket_deleted" : NumberLong(0),
            "ticket_user_id" : NumberLong(2),
            "ticket_used" : NumberLong(0),
            "order_id" : NumberLong(7002),
            "order_code" : NumberLong(517005),
            "order_created" : NumberLong(1430227620),
            "order_deleted" : NumberLong(0),
            "order_sales_pipeline" : NumberLong(18),
            "order_invoice_id" : NumberLong(4202),
            "order_invoice_amount" : 3000.0000000000000000,
            "order_invoice_created" : NumberLong(1430227641),
            "order_invoice_deleted" : NumberLong(0),
            "order_invoice_code" : NumberLong(420155),
            "payment_id" : NumberLong(4365),
            "payment_amount" : 3000.0000000000000000,
            "payment_currency" : NumberLong(4),
            "payment_author_id" : NumberLong(1),
            "payment_type_id" : NumberLong(27),
            "payment_created" : NumberLong(1430227641),
            "payment_deleted" : NumberLong(0),
            "create_time" : ISODate("2015-04-28T13:29:51.326Z")
        }
    ],
    "ok" : 1.0000000000000000
}

我哪里出错了？

Answer 1

你能试试下面的查询吗？它假定付款金额始终相同。看看 addToSet http://docs.mongodb.org/manual/reference/operator/update/addToSet/ .

db.getCollection('ticket_cache').aggregate( 
{ "$match": {"event_id":64} }, 
{ "$group" :
     {"_id":"$order_id", "total": {"$addToSet":"$payment_amount"}}
},     
{"$unwind": "$total"}, 
{"$group": {"_id": "null", "totalOdr": {"$sum": "$total"}}}
)