如何用Sequelize查询多个关联嵌套对象最大时间戳大于指定日期的结果

报错原因 #

1. 第一种方案错误根源：

• 聚合函数MAX()的结果不能直接在WHERE子句中过滤，SQL执行顺序中WHERE的执行优先级高于GROUP BY和聚合计算，过滤聚合结果需要使用HAVING子句
• 未对主表主键做GROUP BY分组，直接调用聚合函数会触发SQL语法错误
• 自定义的聚合字段别名不能直接在同级WHERE条件中引用

2. 第二种方案错误根源：

• Sequelize对HasMany类型关联单独设置limit时，会为每个关联生成独立子查询，关联别名的作用域和外层查询不匹配，导致找不到对应字段
• required: false会走左连接，若主对象没有对应类型的note，关联字段值为NULL，比较判断会不符合预期

可行实现方案 #

推荐使用GROUP BY + HAVING的方案直接满足需求：

// 计算x天前的基准日期，x替换为实际天数
const xDaysAgo = new Date();
xDaysAgo.setDate(xDaysAgo.getDate() - x);

main_object.findAll({
  attributes: [
    // 按需保留主表字段，这里示例取全部主表字段
    ...Object.keys(main_object.rawAttributes).map(field => `main_object.${field}`),
    // 分别计算三类note的最大更新时间
    [Sequelize.fn('MAX', Sequelize.col('note_type1.updated_at')), 'max_note1_updated_at'],
    [Sequelize.fn('MAX', Sequelize.col('note_type2.updated_at')), 'max_note2_updated_at'],
    [Sequelize.fn('MAX', Sequelize.col('note_type3.updated_at')), 'max_note3_updated_at']
  ],
  include: [
    {
      model: this.database.note_type1,
      as: 'note_type1',
      required: false,
      attributes: [] // 不需要返回note详情，避免冗余
    },
    {
      model: this.database.note_type2,
      as: 'note_type2',
      required: false,
      attributes: []
    },
    {
      model: this.database.note_type3,
      as: 'note_type3',
      required: false,
      attributes: []
    }
  ],
  // 按主表主键分组，保证每个主对象只返回一条记录
  group: ['main_object.id'],
  having: {
    // 要求所有note的最大更新时间都大于基准日期
    [Sequelize.Op.and]: [
      Sequelize.where(
        // COALESCE用于处理无对应note的场景，第二个参数是默认值：这里用new Date(0)表示无note时不满足条件，若要无note时视为满足，改为new Date()即可
        Sequelize.fn('COALESCE', Sequelize.col('max_note1_updated_at'), new Date(0)),
        Sequelize.Op.gt,
        xDaysAgo
      ),
      Sequelize.where(
        Sequelize.fn('COALESCE', Sequelize.col('max_note2_updated_at'), new Date(0)),
        Sequelize.Op.gt,
        xDaysAgo
      ),
      Sequelize.where(
        Sequelize.fn('COALESCE', Sequelize.col('max_note3_updated_at'), new Date(0)),
        Sequelize.Op.gt,
        xDaysAgo
      )
    ]
  },
  nest: true
});

如果你的数据库开启了ONLY_FULL_GROUP_BY模式且分组报错，把所有主表查询的非聚合字段都加到group数组中即可。

内容的提问来源于stack exchange，提问作者dylbyl