Sequelize自动注入id字段至SELECT子句导致查询失效问题

正常场景（无关联表） #

原本可直接对Post表进行多属性汇总，代码如下：

Post.findAll({
  attributes: [
    [sequelize.literal(`COALESCE(SUM("Post"."characterCount"), 0)`), 'totalCharacters'],
    [sequelize.literal(`COUNT(DISTINCT "Post"."userId")`), 'userCount'],
  ],
});

生成的SQL查询语句正常：

SELECT 
  COALESCE(SUM("Post"."characterCount"), 0) AS "totalCharacters", 
  COUNT(DISTINCT "Post"."userId") AS "userCount"
FROM 
  "Posts" AS "Post";

异常场景（关联User表并添加条件） #

当关联User表并添加角色过滤条件后，Sequelize会自动将"Post"."id"注入到SELECT子句中，导致聚合查询失效。相关代码：

Post.findAll({
  attributes: [
    [sequelize.literal(`COALESCE(SUM("Post"."characterCount"), 0)`), 'totalCharacters'],
    [sequelize.literal(`COUNT(DISTINCT "Post"."userId")`), 'userCount'],
  ],
  include: [
    {association: 'user', attributes: []},
  ],
  where: {
    '$user.role$': {[Op.not]: 'admin'},
  },
});

生成的异常SQL：

SELECT 
  "Post"."id",
  COALESCE(SUM("Post"."characterCount"), 0) AS "totalCharacters", 
  COUNT(DISTINCT "Post"."userId") AS "userCount"
FROM 
  "Posts" AS "Post" 
  LEFT OUTER JOIN "Users" AS "user" ON "Post"."userId" = "user"."id" 
WHERE 
  "user"."role" != 'admin';

解决方法 #

方案1：添加group选项 #

Sequelize在关联查询时默认会尝试添加主键到SELECT，通过指定group选项（即使是空数组），可以阻止这一行为。示例代码：

Post.findAll({
  attributes: [
    [sequelize.literal(`COALESCE(SUM("Post"."characterCount"), 0)`), 'totalCharacters'],
    [sequelize.literal(`COUNT(DISTINCT "Post"."userId")`), 'userCount'],
  ],
  include: [
    {association: 'user', attributes: []},
  ],
  where: {
    '$user.role$': {[Op.not]: 'admin'},
  },
  group: [] // 也可根据需求指定具体分组字段，比如['"Post"."userId"']
});

方案2：启用raw: true选项 #

设置raw: true后，Sequelize会跳过模型实例构建逻辑，不会自动添加主键字段。示例代码：

Post.findAll({
  attributes: [
    [sequelize.literal(`COALESCE(SUM("Post"."characterCount"), 0)`), 'totalCharacters'],
    [sequelize.literal(`COUNT(DISTINCT "Post"."userId")`), 'userCount'],
  ],
  include: [
    {association: 'user', attributes: []},
  ],
  where: {
    '$user.role$': {[Op.not]: 'admin'},
  },
  raw: true
});

方案3：显式排除主键字段 #

在attributes中通过exclude显式排除id字段，同时保留需要的聚合属性。示例代码：

Post.findAll({
  attributes: {
    exclude: ['id'],
    include: [
      [sequelize.literal(`COALESCE(SUM("Post"."characterCount"), 0)`), 'totalCharacters'],
      [sequelize.literal(`COUNT(DISTINCT "Post"."userId")`), 'userCount'],
    ]
  },
  include: [
    {association: 'user', attributes: []},
  ],
  where: {
    '$user.role$': {[Op.not]: 'admin'},
  },
});

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