使用最小/最大聚合续订“having”子句-Java 学习之路

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

存在具有列“id”，“name”等的“order”表，以及具有列“id”，“order_id”，“start_date”，“end_date”等的“guarantee”表 .

下面是一个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: { '$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 对象中文字替换的支持 . ”

1 回答

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

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

回复于 2024-05-05T23:50:35+08:00

使用最小/最大聚合续订“having”子句

1 回答

相关问题