javascript - 在 where 子句中传递动态查询时出现未处理的拒绝 SequelizeDatabaseError

Question

我正在尝试分解用户的获取请求并将其放入名为查询的变量中。然后使用它的where子句将var查询传递到sequelize的findAll方法中，看起来Sequelize认为我正在寻找一个表CALLED查询，而实际上我正在尝试传递对象。如果我不能很好地解释，我很抱歉，但这是代码和错误:

var info = [];
//link example: localhost:8081/filter/?descripiton=san+francisco&houseType=house&numOfBedroom=3&numOfBathroom=2&houseSize=500&price=1200
exports.filterListings = function(req) {
    //create an object literal which we will return, and has a nested object named filteredList inside.
    //filteredList contains an array named listings where we will put listings that match our filter inside
    let response = {
        filteredList: {listings: []},
    };

    //now we need to see how the user wants us to filter the listings
    const query = req.query;
    //do some logic where we decompose query

    if(query.descripiton != undefined) {
    //info = info + 'descripiton: ' + query.descripiton+', ';
    info.push('descripiton: ' + query.descripiton+', ');
    console.log(info);
    }
    if(query.houseType != undefined) {
    //info = info + 'houseType: ' + query.houseType+', ';
    info.push('houseType: ' + query.houseType+', ');
    //console.log(info);
    }
    if(query.numOfBedroom != undefined) {
    //info = info + 'numOfBedroom: ' + query.numOfBedroom+', ';
    info.push('numOfBedroom: ' + query.numOfBedroom+', ');
    }
    if(query.numOfBathroom != undefined) {
    //info = info + 'numOfBathroom: ' + query.numOfBathroom+', ';
    info.push('numOfBathroom: ' + query.numOfBathroom+', ');
    }
    if(query.houseSize != undefined) {
    //info = info + 'houseSize: ' + query.houseSize+', ';
    info.push('houseSize: ' + query.houseSize+', ');
    }
    if(query.price != undefined) {
    //info = info + 'price: ' + query.price;
    info.push('price: ' + query.price);
    }

然后当我尝试传递信息变量时

listingModel.findAll({
        //error because it wont recognize the variable search nor will it recognize info
        where: {info}
    }).then(listings => {
        // so we loop through listings and insert what we have found into the response (which we are going to return)
    for(var i = 0; i < listings.length; i++) {
        response.filteredList.listings.push(listings[i]);
    }; // loop where we insert data into response done

我希望它根据动态查询查找所有列表，但我收到错误:

Unhandled rejection SequelizeDatabaseError: Unknown column 'Listing.info' in 'where clause'

非常感谢您提供的潜在帮助!

Answer 1

让我们尝试将您的问题一一进行排序。抱歉，双关语:p

而不是使用多个 if 来创建过滤列表。使用for ... in。然后使用该对象数组以及 Sequelize.Op 来创建查询。

示例:

const Op = require('sequelize').Op;

const whereClause = [];
const query = req.query;
for(const key in query) {
  if(query[key] !== '' && query[key] !== null) {
    //object will be pushed to the array like "houseType:big"
    whereClause.push({key:query[key]})
  }
}

//you now have the where clause
//use it in your query with Op.and

listingModel.findAll({
  where: {
    [Op.and]: whereClause,
  }
});

有关使用 Sequelize - Operators 查询的更多信息