Sequelize新增BasketDevice count字段后查询报GROUP BY错误

问题复现 #

我在项目中定义了如下models.js模型文件：

const sequelize = require('../db')
const {DataTypes} = require('sequelize')

const User = sequelize.define('user', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    email: {type: DataTypes.STRING, unique: true},
    password: {type: DataTypes.STRING, unique: true},
    role: {type: DataTypes.STRING, defaultValue: "USER"}
})


const Basket = sequelize.define('basket', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true}
})

const BasketDevice = sequelize.define('basket_device', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
}) 

const Device = sequelize.define('device', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    name: {type: DataTypes.STRING, unique: true, allowNull: false},
    price: {type: DataTypes.INTEGER, allowNull: false},
    rating: {type: DataTypes.INTEGER, allowNull: false, defaultValue: 0},
    img: {type: DataTypes.STRING, unique: true, allowNull: false},
}) 

上述模型分别对应用户表User、商品表Device、用户专属购物车表Basket，以及用于实现多对多关联的购物车-商品关联表BasketDevice。

我编写了如下控制器方法查询指定用户的购物车数据：

async getOne(req, res, next){
      const basket = await Basket.findOne({
          where: {userId: req.user.id},
          include: {
              model: BasketDevice,
              include: {
                  model: Device
              }
          }
      })    
      return res.json(basket)
  }

该接口此前可正常运行，返回的购物车JSON结构如下：

{
    "id": 11,
    "createdAt": "2022-06-27T23:15:28.431Z",
    "updatedAt": "2022-06-27T23:15:28.431Z",
    "userId": 11,
    "basket_devices": [
        {
            "id": 6,
            "createdAt": "2022-06-27T23:15:40.782Z",
            "updatedAt": "2022-06-27T23:15:40.782Z",
            "basketId": 11,
            "deviceId": 1,
            "device": {
                "id": 1,
                "name": "12 pro",
                "price": 10000,
                "rating": 0,
                "img": "43049c4e-e50b-4aec-b6a9-de7fec272f25.jpg",
                "createdAt": "2022-06-18T21:10:18.036Z",
                "updatedAt": "2022-06-18T21:10:18.036Z",
                "typeId": 2,
                "brandId": 2
            }
        }
    ]
}

但当我在BasketDevice表中新增用于存储商品购买数量的count字段，修改模型定义如下：

const BasketDevice = sequelize.define('basket_device', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    count: {type: DataTypes.INTEGER, allowNull: false, defaultValue: 0},
}) 

再次调用接口时出现如下报错：

Executing (default): SELECT "basket".*, "basket_devices"."id" AS "basket_devices.id", "basket_devices"."count" AS "basket_devices.count", "basket_devices"."createdAt" AS "basket_devices.createdAt", "basket_devices"."updatedAt" AS "basket_devices.updatedAt", "basket_devices"."basketId" AS "basket_devices.basketId", "basket_devices"."deviceId" AS "basket_devices.deviceId", "basket_devices->device"."id" AS "basket_devices.device.id", "basket_devices->device"."name" AS "basket_devices.device.name", "basket_devices->device"."price" AS "basket_devices.device.price", "basket_devices->device"."rating" AS "basket_devices.device.rating", "basket_devices->device"."img" AS "basket_devices.device.img", "basket_devices->device"."createdAt" AS "basket_devices.device.createdAt", "basket_devices->device"."updatedAt" AS "basket_devices.device.updatedAt", "basket_devices->device"."typeId" AS "basket_devices.device.typeId", "basket_devices->device"."brandId" AS "basket_devices.device.brandId" FROM (SELECT "basket"."id", "basket"."createdAt", "basket"."updatedAt", "basket"."userId" FROM "baskets" AS "basket" WHERE "basket"."userId" = 11 GROUP BY "basket"."id" LIMIT 1) AS "basket" LEFT OUTER JOIN "basket_devices" AS "basket_devices" ON "basket"."id" = "basket_devices"."basketId" LEFT OUTER JOIN "devices" AS "basket_devices->device" ON "basket_devices"."deviceId" = "basket_devices->device"."id";
node:internal/process/promises:265
            triggerUncaughtException(err, true /* fromPromise */);
            ^

Error
    at Query.run (D:\JavaScript\testNodeReact\server\node_modules\sequelize\lib\dialects\postgres\query.js:50:25)
    at D:\JavaScript\testNodeReact\server\node_modules\sequelize\lib\sequelize.js:311:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async PostgresQueryInterface.select (D:\JavaScript\testNodeReact\server\node_modules\sequelize\lib\dialects\abstract\query-interface.js:407:12)
    at async Function.findAll (D:\JavaScript\testNodeReact\server\node_modules\sequelize\lib\model.js:1134:21)
    at async Function.findOne (D:\JavaScript\testNodeReact\server\node_modules\sequelize\lib\model.js:1228:12)
    at async getOne (D:\JavaScript\testNodeReact\server\controllers\basketController.js:16:24) {
  name: 'SequelizeDatabaseError',
  parent: error: column "basket.id " must appear in the GROUP BY clause or be used in an aggregate function

报错原因 #

这个问题是PostgreSQL的SQL校验规则和Sequelize的生成逻辑bug共同导致的：

• PostgreSQL默认开启ONLY_FULL_GROUP_BY严格校验：只要查询写了GROUP BY，所有SELECT返回的非聚合计算字段，必须全部包含在GROUP BY子句中，否则直接抛出错误。
• 之前BasketDevice是只有主键、关联外键的纯中间表，Sequelize执行多层嵌套关联查询时，不会自动生成带GROUP BY的拆分子查询，所以接口能正常运行。
• 给BasketDevice加了自定义业务字段count之后，Sequelize会把它识别为带自有属性的关联模型，执行findOne时会自动把查询拆成两层子查询，给内层子查询加上GROUP BY "basket"."id"，但子查询里同时查询了createdAt、updatedAt、userId这些没有加到GROUP BY里的字段，直接触发了PostgreSQL的校验报错。

解决方法 #

选下面任意一种方案即可，第一种是日常开发最常用的解法：

1. 查询时显式关闭Sequelize的自动子查询拆分，在查询参数中加subQuery: false，修改后的控制器代码如下：

   async getOne(req, res, next){
         const basket = await Basket.findOne({
             where: {userId: req.user.id},
             subQuery: false, // 新增这行，禁止Sequelize自动拆分子查询生成非法GROUP BY
             include: {
                 model: BasketDevice,
                 include: {
                     model: Device
                 }
             }
         })    
         return res.json(basket)
     }
   

2. 如果你用的Sequelize版本低于6.0，直接升级到最新稳定版即可，这个漏加GROUP BY字段的bug在高版本已经被修复。
3. 也可以手动给查询指定group参数，把主表所有返回字段都加入分组列表，但是这种写法冗余度高，不推荐。

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