javascript - 具有最小/最大聚合的 Sequelize "having"子句

Question

我正在尝试使用 Sequelize v4.3 创建一个查询，该查询将返回连接表中最短日期大于现在的行。数据库是postgres 9.6。

有一个“order”表，其中包含“id”、“name”等列，以及一个“guarantee”表，其中包含“id”、“order_id”、“start_date”、“end_date”等列

下面是一个 SQL 查询示例，它生成我希望的结果:

SELECT
  "order"."id",
  "order"."name",
  min("guarantees"."start_date") AS "start_date",
  max("guarantees"."end_date")   AS "end_date"
FROM "api"."order" AS "order"
  LEFT OUTER JOIN "api"."guarantee" AS "guarantees" ON "order"."id" = "guarantees"."order_id"
WHERE "order"."is_enabled" = TRUE
GROUP BY "order"."id"
HAVING min("guarantees"."start_date") >= now();

这是 Sequelize 查询，它可以完成 90% 的工作，减去“having”子句:

Order.findAll({
  attributes: [
    'id',
    'name',
    [sequelize.fn('min', sequelize.col('guarantees.start_date')), 'start_date'],
    [sequelize.fn('max', sequelize.col('guarantees.end_date')), 'end_date'],
  ],
  group: ['order.id'],
  include: [
    { model: Guarantee, attributes: [] },
  ],
})

除了“having”子句之外，我什么都有。以下是我尝试过的一些事情:

having: { '$min("guarantees"."start_date")$': { $gte: sequelize.fn('now') } }

having: { [sequelize.fn('min', sequelize.col('guarantees.start_date'))]: { $gte: sequelize.fn('now') } }

having: ['$min("guarantees"."start_date") >= now()']

// This is the way described by sequelize author: see https://github.com/sequelize/sequelize/issues/1585#issuecomment-39317886
having: ['min(?) >= ?', '"guarantees"."start_date"', sequelize.fn('now')],

其中大多数都会导致错误:“对 where 对象中文字替换的支持已被删除。”

Answer 1

这可以使用having子句中的Sequelize where函数来完成:

complete: {
  having: sequelize.where(this.sequelize.fn('max', sequelize.col('guarantees.end_date')), {
    $lte: sequelize.fn('now'),
  }),
}