Sequelize聚合函数sum结果异常，仅计算关联模型首条数据

使用Sequelize的sequelize.fn('sum', ...)计算关联training_classes的总价时，结果仅取了关联数据的第一条价格，而非所有关联数据的总和。相关代码及返回结果如下：

原代码 #

Training.findAll({  
  where: {
    owner_id: payload
  },
  attributes: ['id', [sequelize.fn('sum', sequelize.col('training_classes.price')), 'totalPrice']],
  include: [{
    model: TrainingClass,
    as: 'training_classes',
    attributes: ['id', 'price'],
  }],
  group: ['Training.id', 'training_classes.id']
})

原返回结果 #

[
    {
      "id": "45",
      "totalPrice": "300000",
      "training_classes": [
        {
          "id": "94",
          "price": "300000"
        }
      ]
    },
    {
      "id": "8",
      "totalPrice": "1000000",
      "training_classes": [
        {
          "id": "14",
          "price": "1000000"
        },
        {
          "id": "15",
          "price": "300000"
        },
        {
          "id": "16",
          "price": "200000"
        }
      ]
    },
    {
      "id": "47",
      "totalPrice": "100000",
      "training_classes": [
        {
          "id": "97",
          "price": "100000"
        }
      ]
    },
    {
      "id": "39",
      "totalPrice": "1000000",
      "training_classes": [
        {
          "id": "81",
          "price": "1000000"
        },
        {
          "id": "82",
          "price": "300000"
        }
      ]
    },
    {
      "id": "24",
      "totalPrice": "300000",
      "training_classes": [
        {
          "id": "46",
          "price": "300000"
        }
      ]
    },
    {
      "id": "6",
      "totalPrice": "200000",
      "training_classes": [
        {
          "id": "11",
          "price": "200000"
        }
      ]
    },
    {
      "id": "49",
      "totalPrice": "100000",
      "training_classes": [
        {
          "id": "100",
          "price": "100000"
        },
        {
          "id": "99",
          "price": "1000000"
        },
        {
          "id": "101",
          "price": "100000"
        }
      ]
    },
    {
      "id": "20",
      "totalPrice": "200000",
      "training_classes": [
        {
          "id": "38",
          "price": "200000"
        },
        {
          "id": "35",
          "price": "400000"
        },
        {
          "id": "37",
          "price": "500000"
        },
        {
          "id": "36",
          "price": "100000"
        }
      ]
    },
]

问题原因 #

group选项中包含了training_classes.id，这会让SQL按Training.id和training_classes.id的组合分组，每个分组仅对应一条training_classes数据，因此sum计算的结果就是单条数据的价格，而非总和。

解决方案 #

方案一：调整Group By字段 #

移除group中的training_classes.id，仅按Training.id分组，同时处理无关联数据的情况：

Training.findAll({  
  where: {
    owner_id: payload
  },
  attributes: [
    'id', 
    // 使用COALESCE确保无关联数据时totalPrice为0而非null
    [sequelize.fn('COALESCE', sequelize.fn('sum', sequelize.col('training_classes.price')), 0), 'totalPrice']
  ],
  include: [{
    model: TrainingClass,
    as: 'training_classes',
    attributes: ['id', 'price'],
    required: false // 保持左连接，允许无关联数据的Training被查询到
  }],
  group: ['Training.id']
})

注意：该方案可能会导致返回的training_classes数据出现重复（SQL分组后重复关联数据会被保留），若需避免重复，可在include中添加distinct: true。

方案二：使用子查询计算总和 #

通过子查询直接计算每个Training对应的总价，无需设置group，同时能完整返回关联数据：

Training.findAll({  
  where: {
    owner_id: payload
  },
  attributes: [
    'id',
    // 子查询计算当前Training下所有training_classes的价格总和
    [sequelize.literal('(SELECT SUM(price) FROM training_classes WHERE training_classes.training_id = Training.id)'), 'totalPrice']
  ],
  include: [{
    model: TrainingClass,
    as: 'training_classes',
    attributes: ['id', 'price'],
  }]
})

该方案不会出现关联数据重复的问题，适合需要同时获取完整关联列表和总价的场景。

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