php - 如何获取MongoDB中两个字段的差异总和？

我已经有了一个解决方案，但是我正在寻找一个能在MongoServer上完成所有工作的解决方案（因为我认为它会更快，占用的内存更少）
我有一个类方法，比如：

function getTotalOutstandingAmount(){
    $outstandingAmount = 0;
    $subs = $this->mongo->selectCollection('SmsSubscriptions');
    $activeSubsctiptions = $subs->find(array('Status' => 1, '$where' => "this.SubscriptionPayments < this.SubscriptionTotal"));
    foreach ($activeSubsctiptions AS $sub){
        $outstandingAmount += $sub['SubscriptionTotal'] - $sub['SubscriptionPayments'];
    }

    return $outstandingAmount;
}

现在有没有一种方法可以使用mongodb的aggregate方法计算这两个字段的差值之和？还有其他更有效的方法吗？

最佳答案

聚合方法应具有以下管道：

db.SmsSubscriptions.aggregate([
    {
        "$project": {
            "outstandingAmount": {
                "$subtract": ["$SubscriptionTotal", "$SubscriptionPayments"]
            },
            "Status": 1
        }
    },
    { "$match": { "Status": 1, "outstandingAmount": { "$gt": 0 } } },
    {
        "$group": {
            "_id": null,
            "totalOutstandingAmount": { "$sum": "$outstandingAmount" }
        }
    }
])

等效的php示例实现：

$ops = array(
    array(
        "$project" => array(
            "Status" => 1,
            "outstandingAmount" => array(
                "$subtract" => array("$SubscriptionTotal", "$SubscriptionPayments")
                )
            )
        )
    ),
    array(
        "$match" => array(
            "Status" => 1,
            "outstandingAmount" => array("$gt" => 0)
        )
    ),
    array(
        "$group" => array(
            "_id" => null,
            "totalOutstandingAmount" => array("$sum" => "$outstandingAmount" )
        )
    )
);
$results = $this->mongo->selectCollection('SmsSubscriptions')->aggregate($ops);