Блог

У меня есть один запрос фильтра в sequelize с динамическим 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']
    }]
  }]
})
  

Проблема в том, что только предложение where of ticketnotifications входит в фактическое предложение SQL where , остальные условия where tickets и ticketdetails входят в JOIN то, что неверно. Предложение Even 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 в соответствии с фильтрами.
Есть идеи, что я делаю не так?

Вы можете использовать операторы в 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']
    }]
  }]