如何在Sequelize JS的findAll方法中排除关联的group表？

我拥有course、group、student三张表，关联关系为：每个course包含多个group，每个group包含多个student。我希望查询course及其关联的student，但使用findAll方法时会返回包含group层级的结果（如[{courseName,courseCode,groups:[....,students:[...]]}]），我只需要形如[{courseName,courseCode,students:[...]}]的结果。请问如何在findAll方法中排除group表？

表模型代码 #

Course表 #

module.exports = (sequelize, DataTypes) => {
  class course extends Model {
    static associate(models) {
      const { group } = models;

      this.hasMany(group, {
        foreignKey: "cid",
      });
    }
  }
  course.init(
    {
      cid: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      cname: DataTypes.STRING,
      ccode: DataTypes.INTEGER,
    },
    {
      indexes: [
        {
          unique: true,
          fields: ["cname", "ccode"],
        },
      ],
      sequelize,
      modelName: "course",
    }
  );
  return course;
};

Group表 #

module.exports = (sequelize, DataTypes) => {
  class group extends Model {
    static associate(models) {
      const { teacher, studentgroup, student, subject, course } = models;

      this.hasMany(subject);

      this.belongsTo(course, {
        onDelete: "CASCADE",
        foreignKey: "cid",
      });

      this.belongsTo(teacher, {
        onDelete: "CASCADE",
        foreignKey: "tid",
      });
      this.belongsToMany(student, {
        through: studentgroup,
        foreignKey: "gid",
        onDelete: "CASCADE",
      });
    }
  }
  group.init(
    {
      gid: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      gno: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "group",
      timestamps: false,
    }
  );
  return group;
};

Student表 #

module.exports = (sequelize, DataTypes) => {
  class student extends Model {
    static associate(models) {
      const { group, studentgroup, attendance, user, studentparent, parent } =
        models;

      this.belongsToMany(parent, {
        through: studentparent,
        foreignKey: "sid",
      });

      this.belongsTo(user, {
        foreignKey: "uid",
      });
      this.belongsToMany(group, {
        through: studentgroup,
        foreignKey: "sid",
        onDelete: "CASCADE",
      });

      this.hasMany(attendance, {
        foreignKey: "sid",
      });
    }
  }
  student.init(
    {
      sid: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      name: DataTypes.STRING,
      lastname: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "student",
    }
  );
  return student;
};

当前查询代码 #

await course.findAll({
      required: true,
      attributes: ["cname", "ccode"],
      include: [
        {
          required: true,
          model: models["group"],
          include: [
            {
              model: student,
              required: true,
            },
          ],
        },
      ],
    });

解决方案 #

方法一：直接建立Course与Student的关联（推荐） #

由于Course和Student通过Group、studentgroup间接关联，你可以在Course模型中直接定义两者的多对多关联，跳过Group层级的返回。

修改Course模型的associate方法：

static associate(models) {
  const { group, student, studentgroup } = models;

  this.hasMany(group, {
    foreignKey: "cid",
  });

  // 新增：通过group和studentgroup关联Student
  this.belongsToMany(student, {
    through: {
      model: studentgroup,
      include: [group]
    },
    foreignKey: 'cid',
    otherKey: 'sid',
    constraints: false
  });
}

之后即可直接查询Course并包含Student，无需嵌套Group：

await course.findAll({
  required: true,
  attributes: ["cname", "ccode"],
  include: [
    {
      model: models.student,
      required: true,
      // 可选：指定需要的学生字段，减少冗余
      // attributes: ['sid', 'name', 'lastname']
    }
  ],
  distinct: true // 去重，避免同一学生因所属多个Group重复出现
});

方法二：查询后手动处理结果（无需修改模型） #

如果不想调整模型关联，可以在查询完成后，手动提取Student数据并移除Group层级：

// 执行原查询
const coursesWithGroups = await course.findAll({
  required: true,
  attributes: ["cname", "ccode"],
  include: [
    {
      required: true,
      model: models["group"],
      include: [
        {
          model: models.student,
          required: true,
        },
      ],
    },
  ],
});

// 转换结果结构
const courses = coursesWithGroups.map(course => {
  // 合并所有Group下的Student数组
  const allStudents = course.groups.flatMap(group => group.students);
  // 去重，基于sid确保每个学生只出现一次
  const uniqueStudents = [...new Map(allStudents.map(s => [s.sid, s])).values()];
  
  return {
    cname: course.cname,
    ccode: course.ccode,
    students: uniqueStudents
  };
});

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