我正在使用node js + sequelize。我想按选定的选项(如供应商,价格,交货选项等)过滤产品的结果,但我不明白如何使查询准确。
现在我正在这样做,但此方法仅在选择所有过滤器(AND 运算符)时才有效。如果用户只选择 1 个或其中的几个过滤器,我该怎么办?
let vendor = req.query.vendor;
let priceFrom = req.query.priceFrom;
let priceTo = req.query.priceTo;
let delivery = req.query.delivery
Product.findAll({
where: {
vendor_id: {
in: vendor
},
price: {
$between: [priceFrom, priceTo]
},
{
delivery: delivery
}
}
}).then(products => {
res.render('product', {products: products});
console.log(products)
});
我现在看到唯一的一种方法可以让它为每个场合写很多 if-else 语句。
首先,检查查询参数并基于这些查询参数创建一个对象,例如:
let vendor = req.query.vendor;
let priceFrom = req.query.priceFrom;
let priceTo = req.query.priceTo;
let delivery = req.query.delivery
let options = { where: {} };
if (vendor)
options.where.vendor_id = {$in: vendor}
if (priceFrom && priceTo)
options.where.price = {$between: [priceFrom, priceTo]}
if (delivery)
options.where.delivery = delivery
Product.findAll(options)
.then(products => {
console.log(products)
res.render('product', { products: products });
});
你也可以使用像 odata-sequelize 这样的包。它简化了过滤过程。
var parseOData = require("odata-sequelize");
var sequelize = require("sequelize");
var query = parseOData(
"$top=5&$skip=1&$select=Foo,Bar&$filter=Foo eq 'Test' or Bar eq 'Test'&$orderby=Foo desc",
sequelize
);
这将产生
{
attributes: ['Foo', 'Bar'],
limit: 5,
offset: 1,
order: [
['Foo', 'DESC']
],
where: {
[Op.or]: [
{
Foo: {
[Op.eq]: "Test"
}
},
{
Bar: {
[Op.eq]: "Test"
}
}
]
}
}