时间戳

首页 > 解决方案 > Sequelize where 嵌套条件包括进入内部连接

node.js - Sequelize where 嵌套条件包括进入内部连接

问题描述

我有一个动态的后续过滤查询，where clause如下所示，

let ticketnotificationsWhere = {};
if (req.query.department) {
  ticketnotificationsWhere.department = req.query.department;
}
if (req.query.ticketid) {
  ticketnotificationsWhere.ticketid = req.query.ticketid;
}
let ticketWhere = {};
if (req.query.isactionable) {
  ticketWhere.isactionable = req.query.isactionable;
}
if (req.query.maintypeid) {
  ticketWhere.maintypeid = req.query.maintypeid;
}
if (req.query.typeid) {
  ticketWhere.typeid = req.query.typeid;
}
if (req.query.subtypeid) {
  ticketWhere.subtypeid = req.query.subtypeid;
}
let ticketdetailsWhere = {};
if (req.query.customerid) {
  ticketdetailsWhere.customerid = req.query.customerid;
}

return ticketdb.ticketnotifications.findAndCountAll({
  limit: req.query.pageSize,
  offset: req.query.page,
  where:ticketnotificationsWhere,
  attributes: ['id', 'ticketid', 'createdby', 'createddate', 'status'],
  include:[{
    model:ticketdb.tickets,
    required: true,
    where:ticketWhere,
    attributes: ['isactionable'],
    include:[{
      model:ticketdb.ticketdetails,
      required: true,
      where:ticketdetailsWhere,
      attributes: ['poid', 'productid', 'shippingreferenceno', 'customerid', 'issueqty']
    },
    {
      model:ticketdb.maintypemaster,
      attributes: [['name', 'maintypename']]
    },
    {
      model:ticketdb.typemaster,
      attributes: [['name', 'typename']]
    },
    {
      model:ticketdb.subtypemaster,
      attributes: [['name', 'subtypename']],
    },
    {
      model:ticketdb.ticketsource,
      attributes: ['sourcename']
    }]
  }]
})

ticketnotifications问题是实际 SQL子句中只有 where 子句where，rest where 条件tickets和ticketdetailsintoJOIN是错误的。即使是 Limit 子句也不会生成。生成的sql如下，

SELECT count("ticketnotifications"."id") AS "count" FROM
"ticketnotifications" AS "ticketnotifications" INNER JOIN "tickets" AS
"ticket" ON "ticketnotifications"."ticketid" = "ticket"."ticketid" AND
"ticket"."isactionable" = true AND "ticket"."maintypeid" = 18 AND
"ticket"."typeid" = 70 AND "ticket"."subtypeid" = 240 INNER JOIN
"ticketdetails" AS "ticket.ticketdetail" ON "ticket"."ticketid" =
"ticket.ticketdetail"."ticketid" LEFT OUTER JOIN "maintypemaster" AS
"ticket.maintypemaster" ON "ticket"."maintypeid" =
"ticket.maintypemaster"."maintypeid" LEFT OUTER JOIN "typemaster" AS
"ticket.typemaster" ON "ticket"."typeid" =
"ticket.typemaster"."typeid" LEFT OUTER JOIN "subtypemaster" AS
"ticket.subtypemaster" ON "ticket"."subtypeid" =
"ticket.subtypemaster"."subtypeid" LEFT OUTER JOIN "ticketsource" AS
"ticket.ticketsource" ON "ticket"."sourceid" =
"ticket.ticketsource"."sourceid" WHERE "ticketnotifications"."ticketid" = 123;

我期望的是

SELECT count("ticketnotifications"."id") AS "count" FROM
"ticketnotifications" AS "ticketnotifications" INNER JOIN "tickets" AS
"ticket" ON "ticketnotifications"."ticketid" = "ticket"."ticketid" INNER JOIN
"ticketdetails" AS "ticket.ticketdetail" ON "ticket"."ticketid" =
"ticket.ticketdetail"."ticketid" LEFT OUTER JOIN "maintypemaster" AS
"ticket.maintypemaster" ON "ticket"."maintypeid" =
"ticket.maintypemaster"."maintypeid" LEFT OUTER JOIN "typemaster" AS
"ticket.typemaster" ON "ticket"."typeid" =
"ticket.typemaster"."typeid" LEFT OUTER JOIN "subtypemaster" AS
"ticket.subtypemaster" ON "ticket"."subtypeid" =
"ticket.subtypemaster"."subtypeid" LEFT OUTER JOIN "ticketsource" AS
"ticket.ticketsource" ON "ticket"."sourceid" =
"ticket.ticketsource"."sourceid" WHERE "ticketnotifications"."ticketid" = 123 AND
    "ticket"."isactionable" = true AND "ticket"."maintypeid" = 18 AND
    "ticket"."typeid" = 70 AND "ticket"."subtypeid" = 240 
LIMIT 10 OFFSET 0;

我进行了很多搜索并得到了像设置where条件这样的解决方案，$$但这是针对一列的，我想根据过滤器动态添加/设置 where 子句。知道我做错了什么吗？

标签： node.jspostgresqlsequelize.js

解决方案

You can you Operaters in sequelize: Operaters

所以，你的代码可以是这样的：

 return ticketdb.ticketnotifications.findAndCountAll({
  limit: req.query.pageSize,
  offset: req.query.page,
  where: { [Op.and]: [{ticketId: req.query.ticketid}, {isactionable: req.query.isactionable},  {maintypeid: 18}]},
  include:[{
    model:ticketdb.tickets,
    required: true,
    where:ticketWhere,
    attributes: ['isactionable'],
    include:[{
      model:ticketdb.ticketdetails,
      required: true,
      where:ticketdetailsWhere,
    },
    {
      model:ticketdb.maintypemaster,
      attributes: [['name', 'maintypename']]
    },
    {
      model:ticketdb.typemaster,
      attributes: [['name', 'typename']]
    },
    {
      model:ticketdb.subtypemaster,
      attributes: [['name', 'subtypename']],
    },
    {
      model:ticketdb.ticketsource,
      attributes: ['sourcename']
    }]
  }]

希望它可以帮助你。 让我们随心所欲地修改它。

推荐阅读