CODEWITHSUNDEEP
node.jsmongodbmongoosemongodb-queryaggregation-framework
Fabio B.
Fabio B.

Reputation: 9400

Two sums in one query

I have a mongodb (Mongoose entity) which represent a financial entry in my app:

var mongoose = require('mongoose');
var ObjectId = mongoose.Schema.Types.ObjectId;

var transactionSchema = mongoose.Schema({

    description     : String,
    amount          : { type:Number, min:0.01, required:true }, 
    dateEntry       : { type:Date, default:Date.now }, 
    dateAdded       : { type:Date, default:Date.now }, 
    positive        : { type:Boolean, default:false }, 

    category        : { type: ObjectId, ref: 'Category' },
    user            : { type: ObjectId, ref: 'User' }

});

module.exports = mongoose.model('Transaction', transactionSchema);

I would like to give a balance of my transactions: my first approach was making a sum of positive entries, then another sum of negative entries, and finally subtract the negative one from the positive one:

async.parallel({
                totalPositive: function(callback) {
                    var matcher = baseSearch;
                    matcher.positive = true;
                    Transaction.aggregate(
                        { $match: matcher },
                        { $group: {_id:null,sum:{$sum: '$amount'}} },
                        function(err,result) {
                            callback(err,result);
                        }
                    );
                },
                totalNegative: function(callback) {
                    var matcher = baseSearch;
                    matcher.positive = false;
                    Transaction.aggregate(
                        { $match: matcher },
                        { $group: {_id:null,sum:{$sum: '$amount'}} },
                        function(err,result) {
                            callback(err,result);
                        }
                    );
                }
            }, function(err, results) {
                if (err) {
                    return res.json({error:err.message});
                }
                results.balance = results.totalPositive[0].sum - results.totalNegative[0].sum;
                res.json(results);
            });

I wonder if I can save one query by having just one returning both the positive and negative sum.

Any ideas? Or is this the best solution? Thank you

Upvotes: 0

Views: 198

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151092

You can do this with a conditional grouping and then just a final project to do the math:

Transaction.aggregate(
    [
        { "$group": {
            "_id": null,
            "totalPositive": {
                "$cond": [ "$positive", "$amount", 0 ]
            },
            "totalNegative": {
                "$cond": [ "$positive", 0, "$amount" ]
            }
        }},
        { "$project": {
            "totalBalance": { "$subtract": [ "$totalPositive", "$totalNegative" ] }
        }}
    ],
    function(err,result) {

    }
)

So just evaluate whether to $sum the "amount" depending on what "positive" has for either true/false. Then in the following pipeline just use the $subtract operator.

Better yet, just set the "sign" of the value to "sum" in a single group stage:

Transaction.aggregate(
    [
        { "$group": {
            "_id": null,
            "totalBalance": {
                "$sum": {
                    "$cond": [
                         "$positive",
                         "$amount",
                         { "$subtract": [ 0, "$amount" ] }
                    ]
                }
        }}
    ],
    function(err,result) {

    }
);

So there really is no need for the parallel queries when you can just do this in a single stroke.

The "meat" of each case is largely in the $cond operator here, which acts as a ternary operator for evaluation. Link there, but if you are not familiar then this means a way of providing an "inline" if/then/else evaluation. So there is a "test" as the first argument, and where true the second argument is used in the return result, otherwise where false the third argument is returned.

There are other samples available in the documentation at SQL to aggregation mapping which gives several common examples for those who are used to SQL, or are at least used to the "declarative" syntax for problem solving.

General reading on the aggregation framework is recommended, as it is essentially in SQL terms the differnce betweeen SELECT thisfield where anotherfield = "this" to every other permuation of advanced SQL when compared to the basic .find() method invocation.

Upvotes: 1

Related Questions