sequelize 查询的可选参数 [英] Optional parameters on sequelize query

问题描述

早上好.

我对 NodeJS/sequelize 世界很陌生，我目前在尝试在屏幕上显示仪表板时遇到问题.

I'm quite new to NodeJS / sequelize world and I'm currently facing a problem while trying to display a dashboard on screen.

此仪表板具有三个过滤器:两个日期(句点)、客户姓名和员工姓名.用户可以选择无、一个、两个或所有过滤器，我的数据库需要相应地工作.

This dashboard has three filters: two dates (period), client name, and employee name. The user can select none, one, two, or all the filters and my database needs to work accordingly.

话虽如此，我的问题出在 Sequelize 上，因为我不知道如何处理参数不始终"存在的问题.

That being said, my problem is with Sequelize because I don't know how to treat this problem of parameters not being "always" there.

我见过这个问题:

Sequelize 可选 where 子句参数?

但是这个答案不再有效.我还尝试了另一种构建 where 子句的方法，但我也失败了(主要是由于 sequelize 运算符).我尝试的最后一件事是使用包含所有参数的单个查询，但尝试找到一些值(或标志)，使 sequelize 忽略参数，对于参数不存在的情况*，但看起来 Sequelize 没有没有那样的东西.

but this answer doesn't work anymore. I also tried another way of building the where clause, but I failed on it as well (mainly due to sequelize operators). The last thing I tried was to make a single query with all parameters included but try to find some value (or flag) that would make sequelize ignore the parameter, for the case when the parameter was no there*, but it looks like Sequelize doesn't have anything like that.

* 我在这里读过一个问题，该问题的答案是 {} 可以解决问题，但我也尝试过，但没有奏效.

* I've read a question here that has an answer saying that {} would do the trick but I tried that as well but didn't work.

总而言之:我需要做一个可以随着时间改变"的查询，例如:

In summary: I need to make a query that can "change" over time, for example:

Foo.findAll({
  where: { 
  id : 1,
  }
});

Foo.findAll({
  where: { 
  id {
   [Op.in] : [1,2,3,4,5]
  },
  name: "palmeiira",
  }
});

你知道一种不需要使用大量 if/switch 语句的方法吗?

Do you know a way of doing it without the need of using a lot if / switch statements?

我目前使用的是 Sequelize v. 5.5.1.

I'm currently using Sequelize v. 5.5.1.

更新

我尝试按照@Anatoly 的建议进行操作，并创建了一个函数来构建参数.就是这样.(我尝试了一个较小"的版本只是为了测试)

I tried doing as suggested by @Anatoly and created a function to build the parameters. It was something like that. (I tried a "smaller" version just to test)

async function test() {
  const where = {};
  where[Op.and] = [];
  where[Op.eq].push({
    id: {
    [Op.in]: [1,2,3] 
    }
  });

  return where;
}

我将返回值设置为常量:

I setted the return value to a const:

const query = await test()

并尝试了 console.log(query)结果是:{ [Symbol(and)]: [ { id: [Object] } ] }，这让我相信问题出在解析 Op 部分，所以我尝试使用 'Op.and' 和 'Op.in' 来避免这种情况并解决了这个问题，但导致另一个续集说无效值

And tried console.log(query) The result was: { [Symbol(and)]: [ { id: [Object] } ] }, which made me believe that the problem was parsing the Op part so i tried using 'Op.and' and 'Op.in' to avoid that and it solved this problem, but led to another on sequelize that said Invalid value

你知道我的错误在哪里吗?

Do you have any idea where is my error ?

P.S.:@Anatoly 你给我的原始答案的好主意.非常感谢.

P.S.: @Anatoly very nice idea you gave me on original answer. Thank you very much.

推荐答案

如果这三个条件应该一起工作，那么你可以使用 Op.and 和一系列条件:

If these three conditions should work together then you can use Op.and with an array of conditions:

const where = {}

if (datesFilter || clientNameFilter || employeenameFilter) {
  where[Op.and] = []
  if (datesFilter) {
    where[Op.and].push({
      dateField: {
        [Op.between]: [datesFilter.start, datesFilter.finish]
      }
    })
  }
  if (clientNameFilter) {
    where[Op.and].push({
      name: {
        [Op.iLike]: `%${clientNameFilter.value}%`
      }
    })
  }
  if (employeenameFilter) {
    where[Op.and].push({
      employeeName: {
        [Op.iLike]: `%${employeenameFilter.value}%`
      }
    })
  }
}

const dashboardItems = await DashboardItem.findAll(where, {
// some options here
})

如果条件应该作为替代方案起作用，那么只需将 Op.and 替换为 Op.or

If the conditions should work as alternatives then just replace Op.and with Op.or

这篇关于sequelize 查询的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！