Sequelize-MySQL GROUP BY报错：如何用any_value解决ONLY_FULL_GROUP_BY限制？

问题场景 #

编写了一个返回包含最低价格商品的分类的函数，但运行时触发SQL错误：

"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fotoluks.category.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

当前服务器无法关闭ONLY_FULL_GROUP_BY模式，已知可用any_value函数解决，但不知道如何在Sequelize中实现。此前用attributes: []的方法不适用本次场景。

原代码如下：

async getAllWithMinPrice(req, res, next) {
  try {
    const categories = await Category.findAll({
      attributes: ["id", "name"],
      group: ["products.id"],
      include: [
        {
          model: Product,
          attributes: [
            "id",
            "name",
            "pluralName",
            "description",
            "image",
            [Sequelize.fn("MIN", Sequelize.col("price")), "minPrice"],
          ],
          include: [
            {
              model: Type,
              attributes: [],
            },
          ],
        },
      ],
    });
    return res.json(categories);
  } catch (e) {
    return next(ApiError.badRequest(e.message ? e.message : e));
  }
}

解决方案 #

核心是用Sequelize的fn方法调用MySQL的ANY_VALUE函数，将未在GROUP BY中的分类字段包裹起来，绕过ONLY_FULL_GROUP_BY的校验。

修改后的代码：

async getAllWithMinPrice(req, res, next) {
  try {
    const categories = await Category.findAll({
      // 用ANY_VALUE包裹分类的id和name字段
      attributes: [
        [Sequelize.fn('ANY_VALUE', Sequelize.col('category.id')), 'id'],
        [Sequelize.fn('ANY_VALUE', Sequelize.col('category.name')), 'name']
      ],
      group: ["products.id"],
      include: [
        {
          model: Product,
          attributes: [
            "id",
            "name",
            "pluralName",
            "description",
            "image",
            [Sequelize.fn("MIN", Sequelize.col("price")), "minPrice"],
          ],
          include: [
            {
              model: Type,
              attributes: [],
            },
          ],
        },
      ],
    });
    return res.json(categories);
  } catch (e) {
    return next(ApiError.badRequest(e.message ? e.message : e));
  }
}

说明 #

• Sequelize.fn('ANY_VALUE', Sequelize.col('category.id'))会生成SQL中的ANY_VALUE(category.id)，告诉MySQL该字段无需严格遵循GROUP BY的聚合要求，取任意值即可。
• 需明确指定字段所属表（category.id），避免字段歧义。

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