Sequelize报having子句未知列错误，但生成的MySQL查询可正常执行

编写的Sequelize查询代码如下：

const result = await VendorCompany.findAll({
      attributes: [
        "id",
        [Sequelize.fn("COUNT", Sequelize.col("Vendor->Contract.id")), "contractCount"]
      ],
      include: [
        {
          model: Vendor,
          as: "Vendor",
          attributes: [],
          include: [
            {
              model: Company,
              as: "Company",
              attributes: []
            },
            {
              model: Contract,
              as: "Contract",
              attributes: []
            }
          ]
        }
      ],
      where: {
        companyId: 1
      },
      group: ["Vendor.id", "Vendor->Contract.id"],
      having: {
        contractCount: {
          [Op.gte]: 0
        }
      },
      limit: 10,
      subQuery: false // Ensures correct behavior when using LIMIT with GROUP BY
    });

该查询生成的MySQL语句可直接正常运行：

SELECT 
  `VendorCompany`.`id`, 
  COUNT(`Vendor->Contract`.`id`) AS `contractCount` 
FROM 
  `VendorCompanies` AS `VendorCompany` 
  LEFT OUTER JOIN `Users` AS `Vendor` ON `VendorCompany`.`vendorId` = `Vendor`.`id` 
  LEFT OUTER JOIN `Companies` AS `Vendor->Company` ON `Vendor`.`companyId` = `Vendor->Company`.`id` 
  LEFT OUTER JOIN `Contracts` AS `Vendor->Contract` ON `Vendor`.`id` = `Vendor->Contract`.`vendorId` 
WHERE 
  `VendorCompany`.`companyId` = 1 
GROUP BY 
  `Vendor`.`id`, 
  `Vendor->Contract`.`id` 
HAVING 
  `contractCount` >= 0 
LIMIT 
  0, 10;

但执行Sequelize查询时却报错：

SequelizeDatabaseError: Unknown column 'contractCount' in 'having clause'

方案1：直接使用聚合函数表达式替代别名 #

Sequelize解析having对象时，不会自动将别名映射为对应的聚合表达式，需要手动用Sequelize.where构造条件：

having: Sequelize.where(Sequelize.fn("COUNT", Sequelize.col("Vendor->Contract.id")), {
  [Op.gte]: 0
})

方案2：开启MySQL方言的别名支持（Sequelize 6+） #

在Sequelize数据库配置中添加useAliasInHaving选项，允许在having子句中使用别名：

const sequelize = new Sequelize('数据库名', '用户名', '密码', {
  dialect: 'mysql',
  dialectOptions: {
    useAliasInHaving: true
  }
});

额外检查 #

确保已正确导入Sequelize的操作符：

const { Op } = require('sequelize');

虽然MySQL本身允许在HAVING中使用SELECT语句里的别名，但Sequelize在构建查询的过程中，会将having对象中的键视为表的实际列名，而非别名。因此需要通过上述两种方式，让Sequelize正确识别聚合条件。

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