如何在Sequelize中实现多对多关联

Question

我有两个表：Books和Articles，它们之间存在多对多的关系。连接表是BookArticles。

models/books.js

module.exports = function(sequelize, DataTypes) {
  return Food = sequelize.define("Book", {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
      unique: true
    }
  });
}

模型/articles.js

module.exports = function(sequelize, DataTypes) {
  return Food = sequelize.define("Article", {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
      unique: true
    }
  });
}

模型/书文章.js

module.exports = function(sequelize, DataTypes) {
  return Food = sequelize.define("BookArticles", {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
      unique: true
    },
   bookId: {
      type: DataTypes.INTEGER,
      references: 'Book',
      referencesKey: 'id',
      allowNull: false
    },
    ArticleId: {
      type: DataTypes.INTEGER,
      references: 'Article',
      referencesKey: 'id',
      allowNull: false
    },
  });
}

和 models/index.js

m.BookArticles.belongsTo(m.Book);
m.Book.hasMany(m.Article, {through: m.BookArticles});


m.BookArticles.belongsTo(m.Article);
m.Article.hasMany(m.Books, {through: m.BookArticles});

但是我无法获取书籍文章

我该如何获取它？

- Anuj

---

此情境的文档可能会有所帮助：http://docs.sequelizejs.com/class/lib/associations/belongs-to-many.js~BelongsToMany.html - Ulad Kasach

有人可以帮我看一下这个问题吗？https://stackoverflow.com/q/69267021/12071145 - Jahir Hussain

@UladKasach 当前的 belongsToMany() 链接是 https://sequelize.org/api/v6/class/src/associations/belongs-to-many.js~belongstomany - undefined

Answer 1

Sequelize关联表速查表

适用于Sequelize v2/3/4/5

通常我们会被创造的表格和关联方法所迷惑。

注意：定义外键或交叉表名称是可选的。Sequelize会自动创建，但是定义它可以让开发人员读取模型并找出外键/交叉表名称，而不是猜测或需要访问数据库。

简化版

一对一

// foreign key has to be defined on both sides.
Parent.hasOne(Child, {foreignKey: 'Parent_parentId'})
// "Parent_parentId" column will exist in the "belongsTo" table.
Child.belongsTo(Parent, {foreignKey: 'Parent_parentId'})

O:M

Parent.hasMany(Child, {foreignKey: 'Parent_parentId'})
Child.belongsTo(Parent, {foreignKey: 'Parent_parentId'})

N:M

Parent.belongsToMany(
    Child, 
    {
        // this can be string (model name) or a Sequelize Model Object Class
        // through is compulsory since v2
        through: 'Parent_Child',

        // GOTCHA
        // note that this is the Parent's Id, not Child. 
        foreignKey: 'Parent_parentId'
    }
)

/*
The above reads:
"Parents" belongs to many "Children", and is recorded in the "Parent_child" table, using "Parents"'s ID.
*/

Child.belongsToMany(
    Parent, 
    {
        through: 'Parent_Child',

        // GOTCHA
        // note that this is the Child's Id, not Parent.
        foreignKey: 'Child_childId'
    }
)

为什么要使用冗长的“Parent_parentId”而不是只用“parentId”？这是为了让它明显地成为属于“Parent”的外键。在大多数情况下，只使用更简洁的“parentId”就可以了。

关联提供了两个功能：（1）贪婪加载和（2）DAO方法：

1. 包含（贪婪加载）

DB.Parent.findOne({ 
    where: { id: 1 },
    include: [ DB.Child ]
}).then(parent => {

    // you should get `parent.Child` as an array of children. 

})

2. hasOne()、hasMany() 和 belongsTo()/belongsToMany() 方法

关联（Associations）提供了数据访问对象（DAO）方法：

• hasOne():

如果设置了 Parent.hasOne(Child)，则以下方法可用于 parent DAO 实例：

DB.Parent.findOne({ where: { id: 1 } }).then(parent => {

    // `parent` is the DAO
    // you can use any of the methods below:
    parent.getChild
    parent.setChild
    parent.addChild
    parent.createChild
    parent.removeChild
    parent.hasChild

})

在设置Parent.hasMany(Child)时，可用于parent DAO实例的方法：

parent.getChildren,
parent.setChildren,
parent.addChild,
parent.addChildren,
parent.createChild,
parent.removeChild,
parent.hasChild,
parent.hasChildren,

如果设置 Child.belongsTo(Parent)，那么对于 child DAO 实例可用的方法有:

child.getParent,
child.setParent,
child.createParent,

//belongsToMany
child.getParents,
child.setParents,
child.createParents,

你也可以拥有多个关系

自然亲属/子女

// a parent can have many children
Parent.belongsToMany(Child, {
    as: 'Natural',
    through: 'Parent_Child',
    foreignKey: 'Parent_parentId'
})
// a child must at least have 2 parents (natural mother and father)
Child.belongsToMany(Parent, {
    as: 'Natural',
    through: 'Parent_Child',
    foreignKey: 'Child_childId'
})

寄养家庭/儿童

Parent.belongsToMany(Child, {
    as: 'Foster',
    through: 'Parent_Child',
    foreignKey: 'Parent_parentId'
})

Child.belongsToMany(Parent, {
    as: 'Foster',
    through: 'Parent_Child',
    foreignKey: 'Child_childId'
});

以上将创建带有 NaturalId 和 FosterId 的 Parent_Child 交叉表。

- Calvintwr

---

我有一个类似的问题，但是在尝试了上面提到的解决方案后，我无法让它正常工作。当我查询时，会出现错误，但错误对象为空。以下是我的模型关联方式：`sequelize.define('A', {id, name}, A.belongsToMany(models.B, {through: models.A_B, foreignKey: 'a_id'}));sequelize.define('B', {id, name}, B.belongsToMany(models.A, {through: models.A_B, foreignKey: 'b_id'}));sequelize.define('A_B', {id, a_id, b_id}); //映射表A.findAll(query) .then(function (r){ }) .catch (function(e){ });`@Calvintwr @ahiipsa，请帮忙看看？ - optimusPrime

我猜你在.catch()处理程序中有console.log(e)гЂ‚е°ќиЇ•ењЁA_BжЁЎећ‹дё­еЋ»жЋ‰a_idе’Њb_idгЂ‚ - Calvintwr

@Calvintwr谢谢你的解释。N:M关系是否总是需要第三个模型？如果关系只是像用户和项目之间的技术连接一样，那怎么办？跟踪关系的表可以称为user_projects。是否必须创建UserProject模型？如果不是，如何为此表创建迁移文件，并如何在模型User和Project中声明关系？ - robskrob

最好创建UserProject模型，以便您可以在各种情况下使用它。如果您不创建该模型，则Sequelize将自动创建一个默认的交叉表，这仍然可以工作，但是您无法直接查询此表，除非使用原始SQL。无论您是否创建迁移都不是问题。是否选择这样做取决于是否存在更高级别的用途，例如查询它以获取有用信息，甚至存储有关每个交叉关系的有用数据。 - Calvintwr

如果我这样做：Parent.hasMany(Child)，我期望有类似你所说的设置器/获取器：parent.setChildren，但是 Sequelize 知道如何将 "child" 转换为 "children" 吗？也就是说，Sequelize 是否会自动创建 setChildren，即使模型名为 Child？或者我必须使用as 属性 手动配置 Sequelize？编辑 我看到 Sequelize 使用 node.inflection，似乎可以处理 child->children。 - Nate Anderson

Answer 2

删除BookArticles模型并更新关系为：

m.Book.hasMany(m.Article, {through: 'book_articles'});
m.Article.hasMany(m.Books, {through: 'book_articles'});

- Sergey Karasev

---

作废。请参见下面的答案。 - Damon Yuan

我认为@DamonYuan指的是参考了belongsToMany的答案；而不是这个使用了已弃用的hasMany的答案。 - Nate Anderson

Answer 3

这是我解决类似问题的方式：我有两个模型，一个是用户模型。

var user = sequelize.define('user', {
    name: {
        Sequelize.STRING(255)
    },
    email: {
        type: Sequelize.STRING(255),
        unique: true,
        validate: {
            isEmail: true
        }
    }
});

以及一个榜样角色

var Role = sequelize.define('role', {
    name: {
        Sequelize.ENUM('ER', 'ALL', 'DL')
    },
    description: {
        type: Sequelize.TEXT
    }
});

然后我创建了联合模型UserRole。

var UserRole = sequelize.define('user_role', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.ENUM('Admin', 'Staff', 'Customer', 'Owner')
    }
});

注意：如果您不显式定义 UserRole 的 id，Sequelize 将使用两个外键 user_id 和 role_id 作为主键。

然后，我创建了以下的多对多关系：

User.belongsToMany(Role, { as: 'Roles', through: { model: UserRole, unique: false }, foreignKey: 'user_id' });
Role.belongsToMany(User, { as: 'Users', through: { model: UserRole, unique: false }, foreignKey: 'role_id' });

- Buhiire Keneth

---

用户角色是什么？用户角色属于用户和角色吗？用户角色有多个用户和多个角色，你在用户角色里要写些什么？ - PirateApp

Answer 4

带有断言的可运行示例

这里提供一个单源文件可运行示例，展示了https://sequelize.org/master/manual/assocs.html#many-to-many-relationships（archive）中“Foo.hasMany(Bar)”部分提到的每个自动生成的方法。

该模型是一个网站模型，用户可以创建帖子并喜欢其他用户的帖子。

npm install sequelize@6.5.1 sqlite3@5.0.2

main.js

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'db.sqlite3',
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});
User.belongsToMany(Post, {through: 'UserLikesPost'});
Post.belongsToMany(User, {through: 'UserLikesPost'});
await sequelize.sync({force: true});

// Create some users and posts.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})

const post0 = await Post.create({body: 'post0'});
const post1 = await Post.create({body: 'post1'});
const post2 = await Post.create({body: 'post2'});

// Autogenerated add* methods

// Make user0 like post0
await user0.addPost(post0)
// Also works.
//await user0.addPost(post0.id)
// Make user0 and user2 like post1
await post1.addUsers([user0, user2])

// Autogenerated get* methods

// Get posts liked by a user.

const user0Likes = await user0.getPosts({order: [['body', 'ASC']]})
assert(user0Likes[0].body === 'post0');
assert(user0Likes[1].body === 'post1');
assert(user0Likes.length === 2);

const user1Likes = await user1.getPosts({order: [['body', 'ASC']]})
assert(user1Likes.length === 0);

const user2Likes = await user2.getPosts({order: [['body', 'ASC']]})
assert(user2Likes[0].body === 'post1');
assert(user2Likes.length === 1);

// Get users that like a given post.

const post0Likers = await post0.getUsers({order: [['name', 'ASC']]})
assert(post0Likers[0].name === 'user0');
assert(post0Likers.length === 1);

const post1Likers = await post1.getUsers({order: [['name', 'ASC']]})
assert(post1Likers[0].name === 'user0');
assert(post1Likers[1].name === 'user2');
assert(post1Likers.length === 2);

const post2Likers = await post2.getUsers({order: [['name', 'ASC']]})
assert(post2Likers.length === 0);

// Same as getPosts but with the user ID instead of the model object.
{
  const user0Likes = await Post.findAll({
    include: [{
      model: User,
      where: {
        id: user0.id
      }
    }],
  })
  assert(user0Likes[0].body === 'post0');
  assert(user0Likes[1].body === 'post1');
  assert(user0Likes.length === 2);
}

// Yet another way that can be more useful in nested includes.
{
  const user0Likes = (await User.findOne({
    where: {id: user0.id},
    include: [{
      model: Post,
    }],
    order: [[Post, 'body', 'ASC']],
  })).Posts
  assert(user0Likes[0].body === 'post0');
  assert(user0Likes[1].body === 'post1');
  assert(user0Likes.length === 2);
}

// Autogenerated has* methods

// Check if user likes post.
assert( await user0.hasPost(post0))
assert( await user0.hasPost(post0.id)) // same
assert( await user0.hasPost(post1))
assert(!await user0.hasPost(post2))

// Check if post is liked by user.
assert( await post0.hasUser(user0))
assert(!await post0.hasUser(user1))
assert(!await post0.hasUser(user2))

// AND of multiple has checks at once.
assert( await user0.hasPosts([post0, post1]))
// false because user0 does not like post2
assert(!await user0.hasPosts([post0, post1, post2]))

// Autogenerated count* methods
// user0 likes 2 posts.
assert(await user0.countPosts() === 2)
// post0 is liked by 1 user.
assert(await post0.countUsers() === 1)

// Autogenerated remove* method

// user0 doesn't like post0 anymore.
await user0.removePost(post0)
// user0 and user 2 don't like post1 anymore.
await post1.removeUsers([user0, user2])
// Check that no-one likes anything anymore.
assert(await user0.countPosts() === 0)
assert(await post0.countUsers() === 0)

// Autogenerated create* method
// Create a new post and automatically make user0 like it.
const post3 = await user0.createPost({'body': 'post3'})
assert(await user0.hasPost(post3))
assert(await post3.hasUser(user0))

// Autogenerated set* method
// Make user0 like exactly these posts. Unlike anything else.
await user0.setPosts([post1, post2])
assert(!await user0.hasPost(post0))
assert( await user0.hasPost(post1))
assert( await user0.hasPost(post2))
assert(!await user0.hasPost(post3))

await sequelize.close();
})();

GitHub upstream。

生成的SQLite表格如下：

UserLikesPost is the name of the relation table.
Sequelize creates it automatically for us.
On SQLite that table looks like this:
CREATE TABLE `UserLikesPost` (
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  `UserId` INTEGER NOT NULL REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  `PostId` INTEGER NOT NULL REFERENCES `Posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (`UserId`, `PostId`)
);

自我关联（也称自我引用）

这说明如何在表格中实现多对多的自我关联，例如用户关注另一个用户。

基本上：

• 您必须向.belongsToMany添加as:键
• 待办事项：仅生成了addFollow方法，没有生成addFollows方法，为什么？

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'db.sqlite3',
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
User.belongsToMany(User, {through: 'UserFollowUser', as: 'Follows'});
await sequelize.sync({force: true});

// Create some users.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})
const user3 = await User.create({name: 'user3'})

// Make user0 follow user1 and user2
await user0.addFollows([user1, user2])
// Make user2 and user3 follow user0
await user2.addFollow(user0)
await user3.addFollow(user0)

// Check that the follows worked.
const user0Follows = await user0.getFollows({order: [['name', 'ASC']]})
assert(user0Follows[0].name === 'user1');
assert(user0Follows[1].name === 'user2');
assert(user0Follows.length === 2);

const user1Follows = await user1.getFollows({order: [['name', 'ASC']]})
assert(user1Follows.length === 0);

const user2Follows = await user2.getFollows({order: [['name', 'ASC']]})
assert(user2Follows[0].name === 'user0');
assert(user2Follows.length === 1);

const user3Follows = await user3.getFollows({order: [['name', 'ASC']]})
assert(user3Follows[0].name === 'user0');
assert(user3Follows.length === 1);

// Same but with ID instead of object.
{
  const user0Follows = (await User.findOne({
    where: {id: user0.id},
    include: [{model: User, as: 'Follows'}],
  })).Follows
  assert(user0Follows[0].name === 'user1');
  assert(user0Follows[1].name === 'user2');
  assert(user0Follows.length === 2);
}

// has methods
assert(!await user0.hasFollow(user0))
assert(!await user0.hasFollow(user0.id))
assert( await user0.hasFollow(user1))
assert( await user0.hasFollow(user2))
assert(!await user0.hasFollow(user3))

// Count method
assert(await user0.countFollows() === 2)

await sequelize.close();
})();

生成的SQLite表格为：

CREATE TABLE IF NOT EXISTS `UserFollowUser` (
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,=
  `UserId` INTEGER NOT NULL REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  `FollowId` INTEGER NOT NULL REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY (`UserId`, `FollowId`)
);

同时也在Sequelize中如何创建自引用多对多关联？中提到了这个问题。

自定义列的多对多关联

回到用户喜欢帖子的例子，我们还可以通过创建自定义表格来实现相同的结果。

这样可以让我们添加额外的关系参数，例如在这里我们添加一个评分，表示用户有多喜欢这篇文章。

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'db.sqlite3',
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});
const UserLikesPost = sequelize.define('UserLikesPost', {
  UserId: {
    type: DataTypes.INTEGER,
    references: {
      model: User,
      key: 'id'
    }
  },
  PostId: {
    type: DataTypes.INTEGER,
    references: {
      model: Post,
      key: 'id'
    }
  },
  score: {
    type: DataTypes.INTEGER,
  },
});
User.belongsToMany(Post, {through: UserLikesPost});
Post.belongsToMany(User, {through: UserLikesPost});
await sequelize.sync({force: true});

// Create some users and likes.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})

const post0 = await Post.create({body: 'post0'});
const post1 = await Post.create({body: 'post1'});
const post2 = await Post.create({body: 'post2'});

// Make some useres like some posts.
await user0.addPost(post0, {through: {score: 1}})
await user1.addPost(post1, {through: {score: 2}})
await user1.addPost(post2, {through: {score: 3}})

// Find what user0 likes.
const user0Likes = await user0.getPosts({order: [['body', 'ASC']]})
assert(user0Likes[0].body === 'post0');
assert(user0Likes[0].UserLikesPost.score === 1);
assert(user0Likes.length === 1);

// Find what user1 likes.
const user1Likes = await user1.getPosts({order: [['body', 'ASC']]})
assert(user1Likes[0].body === 'post1');
assert(user1Likes[0].UserLikesPost.score === 2);
assert(user1Likes[1].body === 'post2');
assert(user1Likes[1].UserLikesPost.score === 3);
assert(user1Likes.length === 2);

// Where on the custom through table column.
// https://dev59.com/HlkT5IYBdhLWcg3wRNUR
{
  const user1LikesWithScore3 = await Post.findAll({
    include: [{
      model: User,
      where: {
        id: user1.id
      },
      through: {where: {score: 3}},
    }],
  })
  assert(user1LikesWithScore3[0].body === 'post2');
  assert(user1LikesWithScore3[0].UserLikesPost.score === 3);
  assert(user1LikesWithScore3.length === 1);
}

// TODO: this doesn't work. Possible at all in a single addUsers call?
// Make user0 and user2 like post1
// This method automatically generated.
//await post1.addUsers(
//  [user0, user2],
//  {through: [
//    {score: 2},
//    {score: 3},
//  ]}
//)

await sequelize.close();
})();

这个问题已经在以下地方提出：

• Sequelize多对多关系和额外列
• 如何在Sequelize中ORM联接表上的其他列？

并且也在https://sequelize.org/master/manual/advanced-many-to-many.html中有记录。

如何按score查询已被问到：如何在Sequelize中查询多对多关系？

如何使用多个关联进行复杂的JOIN查询？

例如，考虑以下用例：

用户可以关注其他用户，用户可以创建帖子，查找给定用户关注的所有用户的帖子

为了解决这个问题，我们基本上只需要像下面展示的那样嵌套include:语句：

#!/usr/bin/env node

// Find all posts by users that a given user follows.
// https://stackoverflow.com/questions/42632943/sequelize-multiple-where-clause

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'db.sqlite3',
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});
User.belongsToMany(User, {through: 'UserFollowUser', as: 'Follows'});
User.hasMany(Post);
Post.belongsTo(User);
await sequelize.sync({force: true});

// Create data.
const users = await User.bulkCreate([
  {name: 'user0'},
  {name: 'user1'},
  {name: 'user2'},
  {name: 'user3'},
])

const posts = await Post.bulkCreate([
  {body: 'body00', UserId: users[0].id},
  {body: 'body01', UserId: users[0].id},
  {body: 'body10', UserId: users[1].id},
  {body: 'body11', UserId: users[1].id},
  {body: 'body20', UserId: users[2].id},
  {body: 'body21', UserId: users[2].id},
  {body: 'body30', UserId: users[3].id},
  {body: 'body31', UserId: users[3].id},
])

await users[0].addFollows([users[1], users[2]])

// Get all posts by authors that user0 follows.
// The posts are placed inside their respetive authors under .Posts
// so we loop to gather all of them.
{
  const user0Follows = (await User.findByPk(users[0].id, {
    include: [
      {
        model: User,
        as: 'Follows',
        include: [
          {
            model: Post,
          }
        ],
      },
    ],
  })).Follows
  const postsFound = []
  for (const followedUser of user0Follows) {
    postsFound.push(...followedUser.Posts)
  }
  postsFound.sort((x, y) => { return x.body < y.body ? -1 : x.body > y.body ? 1 : 0 })
  assert(postsFound[0].body === 'body10')
  assert(postsFound[1].body === 'body11')
  assert(postsFound[2].body === 'body20')
  assert(postsFound[3].body === 'body21')
  assert(postsFound.length === 4)
}

// With ordering, offset and limit.
// The posts are placed inside their respetive authors under .Posts
// The only difference is that posts that we didn't select got removed.

{
  const user0Follows = (await User.findByPk(users[0].id, {
    offset: 1,
    limit: 2,
    // TODO why is this needed? It does try to make a subquery otherwise, and then it doesn't work.
    // https://selleo.com/til/posts/ddesmudzmi-offset-pagination-with-subquery-in-sequelize-
    subQuery: false,
    include: [
      {
        model: User,
        as: 'Follows',
        include: [
          {
            model: Post,
          }
        ],
      },
    ],
  })).Follows
  assert(user0Follows[0].name === 'user1')
  assert(user0Follows[1].name === 'user2')
  assert(user0Follows.length === 2)
  const postsFound = []
  for (const followedUser of user0Follows) {
    postsFound.push(...followedUser.Posts)
  }
  postsFound.sort((x, y) => { return x.body < y.body ? -1 : x.body > y.body ? 1 : 0 })
  // Note that what happens is that some of the
  assert(postsFound[0].body === 'body11')
  assert(postsFound[1].body === 'body20')
  assert(postsFound.length === 2)

  // Same as above, but now with DESC ordering.
  {
    const user0Follows = (await User.findByPk(users[0].id, {
      order: [[
        {model: User, as: 'Follows'},
        Post,
        'body',
        'DESC'
      ]],
      offset: 1,
      limit: 2,
      subQuery: false,
      include: [
        {
          model: User,
          as: 'Follows',
          include: [
            {
              model: Post,
            }
          ],
        },
      ],
    })).Follows
    // Note how user ordering is also reversed from an ASC.
    // it likely takes the use that has the first post.
    assert(user0Follows[0].name === 'user2')
    assert(user0Follows[1].name === 'user1')
    assert(user0Follows.length === 2)
    const postsFound = []
    for (const followedUser of user0Follows) {
      postsFound.push(...followedUser.Posts)
    }
    // In this very specific data case, this would not be needed.
    // because user2 has the second post body and user1 has the first
    // alphabetically.
    postsFound.sort((x, y) => { return x.body < y.body ? 1 : x.body > y.body ? -1 : 0 })
    // Note that what happens is that some of the
    assert(postsFound[0].body === 'body20')
    assert(postsFound[1].body === 'body11')
    assert(postsFound.length === 2)
  }

  // Here user2 would have no post hits due to the limit,
  // so it is entirely pruned from the user list as desired.
  // Otherwise we would fetch a lot of unwanted user data
  // in a large database.
  const user0FollowsLimit2 = (await User.findByPk(users[0].id, {
    limit: 2,
    subQuery: false,
    include: [
      {
        model: User,
        as: 'Follows',
        include: [ { model: Post } ],
      },
    ],
  })).Follows
  assert(user0FollowsLimit2[0].name === 'user1')
  assert(user0FollowsLimit2.length === 1)

  // Get just the count of the posts authored by users followed by user0.
  // attributes: [] excludes all other data from the SELECT of the queries
  // to optimize things a bit.
  // https://dev59.com/9FoU5IYBdhLWcg3wG0Oy
  {
    const user0Follows = await User.findByPk(users[0].id, {
      attributes: [
        [Sequelize.fn('COUNT', Sequelize.col('Follows.Posts.id')), 'count']
      ],
      include: [
        {
          model: User,
          as: 'Follows',
          attributes: [],
          through: {
            attributes: []
          },
          include: [
            {
              model: Post,
              attributes: [],
            }
          ],
        },
      ],
    })
    assert.strictEqual(user0Follows.dataValues.count, 4);
  }

  // Case in which our post-sorting is needed.
  // TODO: possible to get sequelize to do this for us by returning
  // a flat array directly?
  // Managed with super many to many as shown below.
  // It's not big deal since the LIMITed result should be small,
  // but feels wasteful.
  // https://dev59.com/R53ha4cB1Zd3GeqPcPRP
  // https://github.com/sequelize/sequelize/issues/4419
  {
    await Post.truncate({restartIdentity: true})
    const posts = await Post.bulkCreate([
      {body: 'body0', UserId: users[0].id},
      {body: 'body1', UserId: users[1].id},
      {body: 'body2', UserId: users[2].id},
      {body: 'body3', UserId: users[3].id},
      {body: 'body4', UserId: users[0].id},
      {body: 'body5', UserId: users[1].id},
      {body: 'body6', UserId: users[2].id},
      {body: 'body7', UserId: users[3].id},
    ])
    const user0Follows = (await User.findByPk(users[0].id, {
      order: [[
        {model: User, as: 'Follows'},
        Post,
        'body',
        'DESC'
      ]],
      subQuery: false,
      include: [
        {
          model: User,
          as: 'Follows',
          include: [
            {
              model: Post,
            }
          ],
        },
      ],
    })).Follows
    assert(user0Follows[0].name === 'user2')
    assert(user0Follows[1].name === 'user1')
    assert(user0Follows.length === 2)
    const postsFound = []
    for (const followedUser of user0Follows) {
      postsFound.push(...followedUser.Posts)
    }
    // We need this here, otherwise we would get all user2 posts first:
    // body6, body2, body5, body1
    postsFound.sort((x, y) => { return x.body < y.body ? 1 : x.body > y.body ? -1 : 0 })
    assert(postsFound[0].body === 'body6')
    assert(postsFound[1].body === 'body5')
    assert(postsFound[2].body === 'body2')
    assert(postsFound[3].body === 'body1')
    assert(postsFound.length === 4)
  }
}

await sequelize.close();
})();

超级多对多关系实现“被关注用户发布的文章”查询，无需后处理

超级多对多是指在每个模型与关联表之间显式设置belongsTo/hasMany，除了每个模型的belongsToMany。

这是我发现的唯一一种优雅地实现“被关注用户发布的文章”查询而无需后处理的方法。

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes, Op } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'tmp.' + path.basename(__filename) + '.sqlite',
  define: {
    timestamps: false
  },
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
});
const UserFollowUser = sequelize.define('UserFollowUser', {
    UserId: {
      type: DataTypes.INTEGER,
      references: {
        model: User,
        key: 'id'
      }
    },
    FollowId: {
      type: DataTypes.INTEGER,
      references: {
        model: User,
        key: 'id'
      }
    },
  }
);

// Super many to many.
User.belongsToMany(User, {through: UserFollowUser, as: 'Follows'});
UserFollowUser.belongsTo(User)
User.hasMany(UserFollowUser)

User.hasMany(Post);
Post.belongsTo(User);

await sequelize.sync({force: true});

// Create data.
const users = await User.bulkCreate([
  {name: 'user0'},
  {name: 'user1'},
  {name: 'user2'},
  {name: 'user3'},
])
const posts = await Post.bulkCreate([
  {body: 'body0', UserId: users[0].id},
  {body: 'body1', UserId: users[1].id},
  {body: 'body2', UserId: users[2].id},
  {body: 'body3', UserId: users[3].id},
  {body: 'body4', UserId: users[0].id},
  {body: 'body5', UserId: users[1].id},
  {body: 'body6', UserId: users[2].id},
  {body: 'body7', UserId: users[3].id},
])
await users[0].addFollows([users[1], users[2]])

// Get all the posts by authors that user0 follows.
// without any post process sorting. We only managed to to this
// with a super many to many, because that allows us to specify
// a reversed order in the through table with `on`, since we need to
// match with `FollowId` and not `UserId`.
{
  const postsFound = await Post.findAll({
    order: [[
      'body',
      'DESC'
    ]],
    include: [
      {
        model: User,
        attributes: [],
        required: true,
        include: [
          {
            model: UserFollowUser,
            on: {
              FollowId: {[Op.col]: 'User.id' },
            },
            attributes: [],
            where: {UserId: users[0].id},
          }
        ],
      },
    ],
  })
  assert.strictEqual(postsFound[0].body, 'body6')
  assert.strictEqual(postsFound[1].body, 'body5')
  assert.strictEqual(postsFound[2].body, 'body2')
  assert.strictEqual(postsFound[3].body, 'body1')
  assert.strictEqual(postsFound.length, 4)
}

await sequelize.close();
})();

相关: 如何使用sequelize关联查询并返回扁平化对象

别名: 两个模型间的多对多关联

假设现在用户既可以点赞也可以关注帖子。

为了建立这种关系，我们需要在用户和帖子之间建立两个多对多关联。

然而问题是到目前为止，我们已经将表名User和Post作为关联标识符。

因此，为了消除歧义，我们不得不使用as:参数来创建一个表别名。

const assert = require('assert');
const path = require('path');

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'tmp.' + path.basename(__filename) + '.sqlite',
});

(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
}, {});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
}, {});

User.belongsToMany(Post, {through: 'UserLikesPost', as: 'likedPosts'});
Post.belongsToMany(User, {through: 'UserLikesPost', as: 'likers'});

User.belongsToMany(Post, {through: 'UserFollowsPost', as: 'followedPosts'});
Post.belongsToMany(User, {through: 'UserFollowsPost', as: 'followers'});

await sequelize.sync({force: true});

// Create some users and likes.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})

const post0 = await Post.create({body: 'post0'});
const post1 = await Post.create({body: 'post1'});
const post2 = await Post.create({body: 'post2'});

// Autogenerated add* methods

// Setup likes and follows.
await user0.addLikedPost(post0)
await post1.addLikers([user0, user2])
await user1.addFollowedPosts([post0, post1])
await post1.addFollower(user2)

// Autogenerated get* methods

// Get likes by a user.

const user0Likes = await user0.getLikedPosts({order: [['body', 'ASC']]})
assert(user0Likes[0].body === 'post0');
assert(user0Likes[1].body === 'post1');
assert(user0Likes.length === 2);

const user1Likes = await user1.getLikedPosts({order: [['body', 'ASC']]})
assert(user1Likes.length === 0);

const user2Likes = await user2.getLikedPosts({order: [['body', 'ASC']]})
assert(user2Likes[0].body === 'post1');
assert(user2Likes.length === 1);

// Get users that liked a given post.

const post0Likers = await post0.getLikers({order: [['name', 'ASC']]})
assert(post0Likers[0].name === 'user0');
assert(post0Likers.length === 1);

const post1Likers = await post1.getLikers({order: [['name', 'ASC']]})
assert(post1Likers[0].name === 'user0');
assert(post1Likers[1].name === 'user2');
assert(post1Likers.length === 2);

const post2Likers = await post2.getLikers({order: [['name', 'ASC']]})
assert(post2Likers.length === 0);

// Get follows by a user.

const user0Follows = await user0.getFollowedPosts({order: [['body', 'ASC']]})
assert(user0Follows.length === 0);

const user1Follows = await user1.getFollowedPosts({order: [['body', 'ASC']]})
assert(user1Follows[0].body === 'post0');
assert(user1Follows[1].body === 'post1');
assert(user1Follows.length === 2);

const user2Follows = await user2.getFollowedPosts({order: [['body', 'ASC']]})
assert(user2Follows[0].body === 'post1');
assert(user2Follows.length === 1);

// Get users that followed a given post.

const post0Followers = await post0.getFollowers({order: [['name', 'ASC']]})
assert(post0Followers[0].name === 'user1');
assert(post0Followers.length === 1);

const post1Followers = await post1.getFollowers({order: [['name', 'ASC']]})
assert(post1Followers[0].name === 'user1');
assert(post1Followers[1].name === 'user2');
assert(post1Followers.length === 2);

const post2Followers = await post2.getFollowers({order: [['name', 'ASC']]})
assert(post2Followers.length === 0);

// Same as getLikedPosts but with the user ID instead of the model object.
{
  const user0Likes = await Post.findAll({
    include: [{
      model: User,
      as: 'likers',
      where: {id: user0.id},
    }],
    order: [['body', 'ASC']],
  })
  assert(user0Likes[0].body === 'post0');
  assert(user0Likes[1].body === 'post1');
  assert(user0Likes.length === 2);
}

// Yet another way that can be more useful in nested includes.
{
  const user0Likes = (await User.findOne({
    where: {id: user0.id},
    include: [{
      model: Post,
      as: 'likedPosts',
    }],
    order: [[{model: Post, as: 'likedPosts'}, 'body', 'ASC']],
  })).likedPosts
  assert(user0Likes[0].body === 'post0');
  assert(user0Likes[1].body === 'post1');
  assert(user0Likes.length === 2);
}

await sequelize.close();
})();

奖励: 别名多对一需要 foreignKey

别名多对多可以不用 foreignKey，但多对一则不行：这会创建两个独立的ID，如UserId和authorId。

这是我能让它工作的唯一方法（假设每篇文章都有一个作者和一个评论者）：

User.hasMany(Post, {as: 'authoredPosts', foreignKey: 'authorId'});
Post.belongsTo(User, {as: 'author', foreignKey: 'authorId'});

User.hasMany(Post, {as: 'reviewedPosts', foreignKey: 'reviewerId'});
Post.belongsTo(User, {as: 'reviewer', foreignKey: 'reviewerId'});

如何列出所有自动生成的方法？

如何显示对象的所有方法？ 可以用于此。

JOIN + GROUP BY + 像 COUNT 这样的聚合函数

参见：Sequelize 查询中的内部连接计数

在 Ubuntu 21.04、node.js v14.17.0 上测试通过。

- Ciro Santilli OurBigBook.com

---

这个回答真的帮了我很大的忙。谢谢。 - rickster

@rickster，我很高兴不是只有我被这个库搞糊涂了！ :-) - Ciro Santilli OurBigBook.com

一个疑问 Ciro，我正在做这样的事情 const res = await user.getFollower();，同时我也得到了联接表... 我该如何不在 res 中包含它？有什么想法吗？ - rickster

@rickster user0.getFollower({joinTableAttributes: []}) 看起来可以实现，我会将其添加到示例中。 - Ciro Santilli OurBigBook.com

是的，它完美地工作了。 - rickster

Answer 5

通过表格BookArticles实现M:M关系：

m.Book.belongsToMany(m.Article, {through: m.BookArticles});
m.Article.belongsToMany(m.Books, {through: m.BookArticles});

- Yrysbek Tilekbekov