我想创建一个查询,返回给定年份的收入集合。我的模式是这样的:
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)
});
问题是,我希望查询以这种方式运行:-如果收入为每月,则年份必须等于From.year-如果收入!isMonthly,则年份必须介于dateFrom.year和dateTo.year 之间
到目前为止,我从数据库返回所有内容,并在内存中进行处理,这不是一个最好的解决方案。在SQL Server中,我会使用CASE WHEN语句。我在猫鼬里能用什么?
不幸的是,在您的当前版本中,我想不出任何一个具有模仿case语句/表达式的本地MongoDB运算符的执行ant解决方案。
但是,对于MongoDB Server 3.4和mongoose>=4.7.3,有一个解决方案,您可以使用聚合框架,特别是新的$addFields
阶段和$switch
运算符进行此类查询。
$addFields
阶段等效于$project
步骤,后者显式指定输入文档中的所有现有字段并向文档添加新字段。在您的情况下,您需要它来创建一个新的字段,该字段包含筛选逻辑,然后在$match
查询中使用。
$switch
运算符计算一系列大小写表达式。当找到一个计算结果为true的表达式时,$switch
将执行指定的表达式并脱离控制流。
让我们用一个mongoshell示例来演示这一点:
填充测试集合:
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")
}
])
运行聚合查询
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 } }
])
样本输出
/* 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
}