高级 M:N 关联

在阅读本指南之前，请确保已阅读 关联指南。

让我们从一个 User 和 Profile 之间的多对多关系示例开始。

const User = sequelize.define(
  'user',
  {
    username: DataTypes.STRING,
    points: DataTypes.INTEGER,
  },
  { timestamps: false },
);
const Profile = sequelize.define(
  'profile',
  {
    name: DataTypes.STRING,
  },
  { timestamps: false },
);

定义多对多关系的最简单方法是

User.belongsToMany(Profile, { through: 'User_Profiles' });
Profile.belongsToMany(User, { through: 'User_Profiles' });

通过在上面向 through 传递一个字符串，我们要求 Sequelize 自动生成一个名为 User_Profiles 的模型作为通过表（也称为连接表），它只有两列：userId 和 profileId。将在这两列上建立一个复合唯一键。

我们也可以自己定义一个模型作为通过表使用。

const User_Profile = sequelize.define('User_Profile', {}, { timestamps: false });
User.belongsToMany(Profile, { through: User_Profile });
Profile.belongsToMany(User, { through: User_Profile });

以上具有完全相同的效果。请注意，我们没有在 User_Profile 模型上定义任何属性。将它传递到 belongsToMany 调用中告诉 Sequelize 自动创建两个属性 userId 和 profileId，就像其他关联也导致 Sequelize 自动向其中一个涉及的模型添加一列一样。

但是，自己定义模型有很多优点。例如，我们可以通过表上定义更多列

const User_Profile = sequelize.define(
  'User_Profile',
  {
    selfGranted: DataTypes.BOOLEAN,
  },
  { timestamps: false },
);
User.belongsToMany(Profile, { through: User_Profile });
Profile.belongsToMany(User, { through: User_Profile });

有了这个，我们现在可以通过表跟踪额外的信息，即 selfGranted 布尔值。例如，在调用 user.addProfile() 时，我们可以使用 through 选项传递额外列的值。

示例

const amidala = await User.create({ username: 'p4dm3', points: 1000 });
const queen = await Profile.create({ name: 'Queen' });
await amidala.addProfile(queen, { through: { selfGranted: false } });
const result = await User.findOne({
  where: { username: 'p4dm3' },
  include: Profile,
});
console.log(result);

输出

{
  "id": 4,
  "username": "p4dm3",
  "points": 1000,
  "profiles": [
    {
      "id": 6,
      "name": "queen",
      "User_Profile": {
        "userId": 4,
        "profileId": 6,
        "selfGranted": false
      }
    }
  ]
}

你也可以在单个 create 调用中创建所有关系。

示例

const amidala = await User.create(
  {
    username: 'p4dm3',
    points: 1000,
    profiles: [
      {
        name: 'Queen',
        User_Profile: {
          selfGranted: true,
        },
      },
    ],
  },
  {
    include: Profile,
  },
);

const result = await User.findOne({
  where: { username: 'p4dm3' },
  include: Profile,
});

console.log(result);

输出

{
  "id": 1,
  "username": "p4dm3",
  "points": 1000,
  "profiles": [
    {
      "id": 1,
      "name": "Queen",
      "User_Profile": {
        "selfGranted": true,
        "userId": 1,
        "profileId": 1
      }
    }
  ]
}

你可能已经注意到 User_Profiles 表没有 id 字段。如上所述，它有一个复合唯一键。这个复合唯一键的名称由 Sequelize 自动选择，但可以通过 uniqueKey 选项自定义。

User.belongsToMany(Profile, {
  through: User_Profiles,
  uniqueKey: 'my_custom_unique',
});

另一种可能性，如果需要，是强制通过表具有像其他标准表一样的主键。为此，只需在模型中定义主键即可

const User_Profile = sequelize.define(
  'User_Profile',
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    selfGranted: DataTypes.BOOLEAN,
  },
  { timestamps: false },
);
User.belongsToMany(Profile, { through: User_Profile });
Profile.belongsToMany(User, { through: User_Profile });

以上仍然会创建两列 userId 和 profileId，当然，但不是在它们上设置复合唯一键，而是模型会使用它的 id 列作为主键。其他一切仍然可以正常工作。

通过表与普通表以及“超级多对多关联”

现在我们将比较上面展示的最后一个多对多设置的使用情况与通常的一对多关系，以便最终得出“超级多对多关系”的概念。

模型回顾（略微重命名）

为了让事情更容易理解，让我们将 User_Profile 模型重命名为 grant。请注意，一切工作方式与以前相同。我们的模型是

const User = sequelize.define(
  'user',
  {
    username: DataTypes.STRING,
    points: DataTypes.INTEGER,
  },
  { timestamps: false },
);

const Profile = sequelize.define(
  'profile',
  {
    name: DataTypes.STRING,
  },
  { timestamps: false },
);

const Grant = sequelize.define(
  'grant',
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    selfGranted: DataTypes.BOOLEAN,
  },
  { timestamps: false },
);

我们使用 Grant 模型作为通过表建立了 User 和 Profile 之间的多对多关系

User.belongsToMany(Profile, { through: Grant });
Profile.belongsToMany(User, { through: Grant });

这会自动将 userId 和 profileId 列添加到 Grant 模型中。

注意：如上所示，我们已选择强制 grant 模型具有单个主键（通常称为 id）。这是即将定义的超级多对多关系所必需的。

改为使用一对多关系

如果我们不设置上面定义的多对多关系，而是执行以下操作会怎样？

// Setup a One-to-Many relationship between User and Grant
User.hasMany(Grant);
Grant.belongsTo(User);

// Also setup a One-to-Many relationship between Profile and Grant
Profile.hasMany(Grant);
Grant.belongsTo(Profile);

结果本质上是一样的！这是因为 User.hasMany(Grant) 和 Profile.hasMany(Grant) 会自动分别将 userId 和 profileId 列添加到 Grant 中。

这表明一个多对多关系与两个一对多关系并没有太大区别。数据库中的表看起来一样。

唯一的区别是当你尝试使用 Sequelize 进行急切加载时。

// With the Many-to-Many approach, you can do:
User.findAll({ include: Profile });
Profile.findAll({ include: User });
// However, you can't do:
User.findAll({ include: Grant });
Profile.findAll({ include: Grant });
Grant.findAll({ include: User });
Grant.findAll({ include: Profile });

// On the other hand, with the double One-to-Many approach, you can do:
User.findAll({ include: Grant });
Profile.findAll({ include: Grant });
Grant.findAll({ include: User });
Grant.findAll({ include: Profile });
// However, you can't do:
User.findAll({ include: Profile });
Profile.findAll({ include: User });
// Although you can emulate those with nested includes, as follows:
User.findAll({
  include: {
    model: Grant,
    include: Profile,
  },
}); // This emulates the `User.findAll({ include: Profile })`, however
// the resulting object structure is a bit different. The original
// structure has the form `user.profiles[].grant`, while the emulated
// structure has the form `user.grants[].profiles[]`.

两全其美：超级多对多关系

我们可以简单地将上面显示的两种方法结合起来！

// The Super Many-to-Many relationship
User.belongsToMany(Profile, { through: Grant });
Profile.belongsToMany(User, { through: Grant });
User.hasMany(Grant);
Grant.belongsTo(User);
Profile.hasMany(Grant);
Grant.belongsTo(Profile);

这样，我们可以进行各种急切加载

// All these work:
User.findAll({ include: Profile });
Profile.findAll({ include: User });
User.findAll({ include: Grant });
Profile.findAll({ include: Grant });
Grant.findAll({ include: User });
Grant.findAll({ include: Profile });

我们甚至可以执行各种深度嵌套的包含

User.findAll({
  include: [
    {
      model: Grant,
      include: [User, Profile],
    },
    {
      model: Profile,
      include: {
        model: User,
        include: {
          model: Grant,
          include: [User, Profile],
        },
      },
    },
  ],
});

别名和自定义键名称

与其他关系类似，可以为多对多关系定义别名。

在继续之前，请回顾belongsTo 的别名示例，该示例位于关联指南中。请注意，在这种情况下，定义关联会影响包含执行的方式（即传递关联名称）以及 Sequelize 为外键选择的名称（在该示例中，在 Ship 模型上创建了 leaderId）。

为 belongsToMany 关联定义别名也会影响包含执行的方式

Product.belongsToMany(Category, {
  as: 'groups',
  through: 'product_categories',
});
Category.belongsToMany(Product, { as: 'items', through: 'product_categories' });

// [...]

await Product.findAll({ include: Category }); // This doesn't work

await Product.findAll({
  // This works, passing the alias
  include: {
    model: Category,
    as: 'groups',
  },
});

await Product.findAll({ include: 'groups' }); // This also works

但是，在这里定义别名与外键名称无关。通过表中创建的两个外键的名称仍然由 Sequelize 根据正在关联的模型的名称构建。这可以通过检查上面示例中为通过表生成的 SQL 来轻松看到

CREATE TABLE IF NOT EXISTS `product_categories` (
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `productId` INTEGER NOT NULL REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  `categoryId` INTEGER NOT NULL REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (`productId`, `categoryId`)
);

我们可以看到外键是 productId 和 categoryId。要更改这些名称，Sequelize 接受选项 foreignKey 和 otherKey（即，foreignKey 定义通过关系中源模型的键，otherKey 定义目标模型的键）

Product.belongsToMany(Category, {
  through: 'product_categories',
  foreignKey: 'objectId', // replaces `productId`
  otherKey: 'typeId', // replaces `categoryId`
});
Category.belongsToMany(Product, {
  through: 'product_categories',
  foreignKey: 'typeId', // replaces `categoryId`
  otherKey: 'objectId', // replaces `productId`
});

生成的 SQL

CREATE TABLE IF NOT EXISTS `product_categories` (
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `objectId` INTEGER NOT NULL REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  `typeId` INTEGER NOT NULL REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (`objectId`, `typeId`)
);

如上所示，当使用两个 belongsToMany 调用定义多对多关系（这是标准方法）时，应在两个调用中适当地提供 foreignKey 和 otherKey 选项。如果只在一个调用中传递这些选项，则 Sequelize 行为将不可靠。

自引用

Sequelize 直观地支持自引用多对多关系

Person.belongsToMany(Person, { as: 'Children', through: 'PersonChildren' });
// This will create the table PersonChildren which stores the ids of the objects.

指定通过表的属性

默认情况下，在急切加载多对多关系时，Sequelize 将以以下结构返回数据（基于本指南中的第一个示例）

// User.findOne({ include: Profile })
{
  "id": 4,
  "username": "p4dm3",
  "points": 1000,
  "profiles": [
    {
      "id": 6,
      "name": "queen",
      "grant": {
        "userId": 4,
        "profileId": 6,
        "selfGranted": false
      }
    }
  ]
}

请注意，外部对象是一个 User，它有一个名为 profiles 的字段，它是一个 Profile 数组，这样每个 Profile 都会带有一个名为 grant 的额外字段，它是一个 Grant 实例。这是 Sequelize 在从多对多关系进行急切加载时创建的默认结构。

但是，如果你只想要通过表中的一些属性，可以在 attributes 选项中提供一个包含你想要属性的数组。例如，如果你只想要通过表中的 selfGranted 属性

User.findOne({
  include: {
    model: Profile,
    through: {
      attributes: ['selfGranted'],
    },
  },
});

输出

{
  "id": 4,
  "username": "p4dm3",
  "points": 1000,
  "profiles": [
    {
      "id": 6,
      "name": "queen",
      "grant": {
        "selfGranted": false
      }
    }
  ]
}

如果你根本不想要嵌套的 grant 字段，请使用 attributes: []

User.findOne({
  include: {
    model: Profile,
    through: {
      attributes: [],
    },
  },
});

输出

{
  "id": 4,
  "username": "p4dm3",
  "points": 1000,
  "profiles": [
    {
      "id": 6,
      "name": "queen"
    }
  ]
}

如果你使用的是混合（例如 user.getProfiles()）而不是查找器方法（例如 User.findAll()），则必须使用 joinTableAttributes 选项

someUser.getProfiles({ joinTableAttributes: ['selfGranted'] });

输出

[
  {
    "id": 6,
    "name": "queen",
    "grant": {
      "selfGranted": false
    }
  }
]

多对多对多关系及超越

假设您正在尝试模拟一个游戏锦标赛。有玩家和队伍。队伍参加比赛。但是，玩家可以在锦标赛中途更换队伍（但不能在比赛中途更换）。因此，对于特定的一场比赛，会有特定的队伍参与，并且这些队伍中的每一个都有一组球员（对于那场比赛）。

因此，我们首先定义三个相关的模型

const Player = sequelize.define('Player', { username: DataTypes.STRING });
const Team = sequelize.define('Team', { name: DataTypes.STRING });
const Game = sequelize.define('Game', { name: DataTypes.STRING });

现在，问题是：如何将它们关联起来？

首先，我们注意到

• 一场比赛有许多队伍与之相关联（正在参加那场比赛的队伍）；
• 一支队伍可能参加过许多场比赛。

以上观察表明，我们需要在比赛和队伍之间建立多对多关系。让我们使用本指南前面解释的超级多对多关系

// Super Many-to-Many relationship between Game and Team
const GameTeam = sequelize.define('GameTeam', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false,
  },
});
Team.belongsToMany(Game, { through: GameTeam });
Game.belongsToMany(Team, { through: GameTeam });
GameTeam.belongsTo(Game);
GameTeam.belongsTo(Team);
Game.hasMany(GameTeam);
Team.hasMany(GameTeam);

关于玩家的部分更棘手。我们注意到，形成一支队伍的球员集合不仅取决于队伍（显然），还取决于考虑的比赛。因此，我们不希望在玩家和队伍之间建立多对多关系。我们也不希望在玩家和比赛之间建立多对多关系。与其将玩家与这些模型中的任何一个关联起来，我们需要的其实是玩家与类似“队伍-比赛配对约束”的东西之间的关联，因为是配对（队伍加比赛）定义了哪些玩家属于那里。所以我们正在寻找的东西恰好是联接模型 GameTeam 本身！并且，我们注意到，由于给定的“比赛-队伍配对”指定了许多玩家，另一方面，同一个玩家可以参与许多“比赛-队伍配对”，因此我们需要在玩家和 GameTeam 之间建立多对多关系！

为了提供最大的灵活性，让我们在这里再次使用超级多对多关系构造

// Super Many-to-Many relationship between Player and GameTeam
const PlayerGameTeam = sequelize.define('PlayerGameTeam', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false,
  },
});
Player.belongsToMany(GameTeam, { through: PlayerGameTeam });
GameTeam.belongsToMany(Player, { through: PlayerGameTeam });
PlayerGameTeam.belongsTo(Player);
PlayerGameTeam.belongsTo(GameTeam);
Player.hasMany(PlayerGameTeam);
GameTeam.hasMany(PlayerGameTeam);

以上关联精确地实现了我们想要的结果。这是一个完整的可运行示例

const { Sequelize, Op, Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:', {
  define: { timestamps: false }, // Just for less clutter in this example
});
const Player = sequelize.define('Player', { username: DataTypes.STRING });
const Team = sequelize.define('Team', { name: DataTypes.STRING });
const Game = sequelize.define('Game', { name: DataTypes.STRING });

// We apply a Super Many-to-Many relationship between Game and Team
const GameTeam = sequelize.define('GameTeam', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false,
  },
});
Team.belongsToMany(Game, { through: GameTeam });
Game.belongsToMany(Team, { through: GameTeam });
GameTeam.belongsTo(Game);
GameTeam.belongsTo(Team);
Game.hasMany(GameTeam);
Team.hasMany(GameTeam);

// We apply a Super Many-to-Many relationship between Player and GameTeam
const PlayerGameTeam = sequelize.define('PlayerGameTeam', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false,
  },
});
Player.belongsToMany(GameTeam, { through: PlayerGameTeam });
GameTeam.belongsToMany(Player, { through: PlayerGameTeam });
PlayerGameTeam.belongsTo(Player);
PlayerGameTeam.belongsTo(GameTeam);
Player.hasMany(PlayerGameTeam);
GameTeam.hasMany(PlayerGameTeam);

(async () => {
  await sequelize.sync();
  await Player.bulkCreate([
    { username: 's0me0ne' },
    { username: 'empty' },
    { username: 'greenhead' },
    { username: 'not_spock' },
    { username: 'bowl_of_petunias' },
  ]);
  await Game.bulkCreate([
    { name: 'The Big Clash' },
    { name: 'Winter Showdown' },
    { name: 'Summer Beatdown' },
  ]);
  await Team.bulkCreate([
    { name: 'The Martians' },
    { name: 'The Earthlings' },
    { name: 'The Plutonians' },
  ]);

  // Let's start defining which teams were in which games. This can be done
  // in several ways, such as calling `.setTeams` on each game. However, for
  // brevity, we will use direct `create` calls instead, referring directly
  // to the IDs we want. We know that IDs are given in order starting from 1.
  await GameTeam.bulkCreate([
    { GameId: 1, TeamId: 1 }, // this GameTeam will get id 1
    { GameId: 1, TeamId: 2 }, // this GameTeam will get id 2
    { GameId: 2, TeamId: 1 }, // this GameTeam will get id 3
    { GameId: 2, TeamId: 3 }, // this GameTeam will get id 4
    { GameId: 3, TeamId: 2 }, // this GameTeam will get id 5
    { GameId: 3, TeamId: 3 }, // this GameTeam will get id 6
  ]);

  // Now let's specify players.
  // For brevity, let's do it only for the second game (Winter Showdown).
  // Let's say that that s0me0ne and greenhead played for The Martians, while
  // not_spock and bowl_of_petunias played for The Plutonians:
  await PlayerGameTeam.bulkCreate([
    // In 'Winter Showdown' (i.e. GameTeamIds 3 and 4):
    { PlayerId: 1, GameTeamId: 3 }, // s0me0ne played for The Martians
    { PlayerId: 3, GameTeamId: 3 }, // greenhead played for The Martians
    { PlayerId: 4, GameTeamId: 4 }, // not_spock played for The Plutonians
    { PlayerId: 5, GameTeamId: 4 }, // bowl_of_petunias played for The Plutonians
  ]);

  // Now we can make queries!
  const game = await Game.findOne({
    where: {
      name: 'Winter Showdown',
    },
    include: {
      model: GameTeam,
      include: [
        {
          model: Player,
          through: { attributes: [] }, // Hide unwanted `PlayerGameTeam` nested object from results
        },
        Team,
      ],
    },
  });

  console.log(`Found game: "${game.name}"`);
  for (let i = 0; i < game.GameTeams.length; i++) {
    const team = game.GameTeams[i].Team;
    const players = game.GameTeams[i].Players;
    console.log(`- Team "${team.name}" played game "${game.name}" with the following players:`);
    console.log(players.map(p => `--- ${p.username}`).join('\n'));
  }
})();

输出

Found game: "Winter Showdown"
- Team "The Martians" played game "Winter Showdown" with the following players:
--- s0me0ne
--- greenhead
- Team "The Plutonians" played game "Winter Showdown" with the following players:
--- not_spock
--- bowl_of_petunias

因此，这就是我们在 Sequelize 中在三个模型之间实现“多对多对多”关系的方法，利用了超级多对多关系技术！

这个想法可以递归地应用于更复杂的多对多对...对多关系（尽管在某个时候查询可能会变慢）。