CODEWITHSUNDEEP
node.jsmongodbmongoose
Łukasz Trzewik
Łukasz Trzewik

Reputation: 1165

Mongoose conditional query - Case when like statement

I want to create a query that will return a collection of incomes for a given year. My schema is like this:

export const IncomeSchema = new mongoose.Schema({
name: { type: String, required: true },
amount: { type: Number, required: true },
amountAfterTax: { type: Number, required: false },
dateFrom: {
    month: Number,
    year: Number
},
dateTo: {
    month: Number,
    year: Number
},
isMonthly: { type: Boolean, required: true },
userId: { type: mongoose.Schema.Types.ObjectId, ref: 'User' }
}, { toJSON: { virtuals: true } });

// somehow use the req.params.year
return DB.Incomes.find({ userId: req.user ).exec().then(incomes => {
let incomesForMonth = incomes.filter(income => !income.isMonthly ? (income.dateFrom.year== req.params.year)
     : (income.dateFrom.year <= req.params.year && income.dateTo.year >= req.params.year)
});

The thing is that i want the query to run that way: - if income isMonthly then the year has to be equal dateFrom.year - if income !isMonthly then the year has to be between dateFrom.year and dateTo.year

Till now i was returning everything from db and processed them in memory, which is not a greatest solution. In SQL Server i would use the CASE WHEN statement. What can i use in mongoose?

Upvotes: 2

Views: 1292

Answers (1)

chridam
chridam

Reputation: 103375

Unfortunately with your current version I can't think of any perform-ant solution that has native MongoDB operators which mimic the case statements/expressions.

However, for MongoDB Server 3.4 and mongoose >=4.7.3, there is a solution where you can use the aggregation framework in particular the new $addFields stage and $switch operator for such queries.

The $addFields stage is equivalent to a $project stage that explicitly specifies all existing fields in the input documents and adds new fields to documents. In your case you need it to create a new field field that holds the filter logic and is then used in your $match query.

The $switch operator evaluates a series of case expressions. When it finds an expression which evaluates to true, $switch executes a specified expression and breaks out of the control flow.

Let's demonstrate this with a mongo shell example:

Populate Test Collection:

db.test.insert([
    {
        name: "foo",
        amount: 4,
        amountAfterTax: 3.2,
        dateFrom: {
            month: 11,
            year: 2016
        },
        dateTo: {
            month: 2,
            year: 2017
        },
        isMonthly: true,
        userId: ObjectId("5864b49ab5a589b63ee298e8")  
    },
        {
        name: "test",
        amount: 547.74,
        amountAfterTax: 507.15,
        dateFrom: {
            month: 4,
            year: 2016
        },
        dateTo: {
            month: 4,
            year: 2017
        },
        isMonthly: true,
        userId: ObjectId("5864b49ab5a589b63ee298e8")  
    },
        {
        name: "bar",
        amount: 56,
        amountAfterTax: 47.54,
        dateFrom: {
            month: 5,
            year: 2016
        },
        dateTo: {
            month: 7,
            year: 2016
        },
        isMonthly: false,
        userId: ObjectId("5864b49ab5a589b63ee298e8")  
    }
])

Run Aggregate Query

year = 2016;
db.test.aggregate([
    {
        "$addFields": {
            "incomesForMonth": {
                "$switch": {
                    "branches": [
                        { 
                            "case": "$isMonthly",  /* same as "case": { "$eq": [ "$isMonthly", true ] }, */
                            "then": { "$eq": [ "$dateFrom.year", year ] }
                        },
                        { 
                            "case": { "$eq": [ "$isMonthly", false ] }, 
                            "then": {
                                "$and": [
                                    { "$lte": [ "$dateFrom.year", year ] },
                                    { "$gte": [ "$dateTo.year", year ] }
                                ]
                            }
                        }
                    ]
                }
            }
        }
    },
    { "$match": { "incomesForMonth": true } }
])

Sample Output

/* 1 */
{
    "_id" : ObjectId("586ea7bafedfbcfd0ed15f9a"),
    "name" : "foo",
    "amount" : 4.0,
    "amountAfterTax" : 3.2,
    "dateFrom" : {
        "month" : 11.0,
        "year" : 2016.0
    },
    "dateTo" : {
        "month" : 2.0,
        "year" : 2017.0
    },
    "isMonthly" : true,
    "userId" : ObjectId("5864b49ab5a589b63ee298e8"),
    "incomesForMonth" : true
}

/* 2 */
{
    "_id" : ObjectId("586ea7bafedfbcfd0ed15f9b"),
    "name" : "test",
    "amount" : 547.74,
    "amountAfterTax" : 507.15,
    "dateFrom" : {
        "month" : 4.0,
        "year" : 2016.0
    },
    "dateTo" : {
        "month" : 4.0,
        "year" : 2017.0
    },
    "isMonthly" : true,
    "userId" : ObjectId("5864b49ab5a589b63ee298e8"),
    "incomesForMonth" : true
}

/* 3 */
{
    "_id" : ObjectId("586ea7bafedfbcfd0ed15f9c"),
    "name" : "bar",
    "amount" : 56.0,
    "amountAfterTax" : 47.54,
    "dateFrom" : {
        "month" : 5.0,
        "year" : 2016.0
    },
    "dateTo" : {
        "month" : 7.0,
        "year" : 2016.0
    },
    "isMonthly" : false,
    "userId" : ObjectId("5864b49ab5a589b63ee298e8"),
    "incomesForMonth" : true
}

Upvotes: 2

Related Questions