SequelizeJS - hasMany 到 hasMany 在同一张表上与连接表

2022-01-19 00:00:00 mysql sequelize.js

我的问题很简单:

我有一个名为 users 的表.这些用户可以有很多联系人.这些联系人是其他用户.

I got a table named users. Those users can have a lot of contacts. Those contacts are other users.

所以我有一个名为 userHasContacts 的表，其中包含所有者的 ID (userID) 和联系人的 ID (contactID).
这两个外键都引用了 users 表.

So I have a table named userHasContacts, with the id of the owner (userID), and the id of the contact (contactID).
Both of those foreign keys are referencing users table.

这是我的漂亮图表:

              ----------------  
______________|____      ____|____
| userHasContacts |      | users |
-------------------      ---------
| #userID         |      | id    |
| #contactID      |      ---------
-------------------          |
              |              |
              ----------------

在续集中，按照我的逻辑，我会写:

In sequelize, in my logic, I would write:

Users.hasMany(Users, {foreignKey: 'userID', joinTableName: 'userHasContacts'} );
Users.hasMany(Users, {as: 'Contacts', foreignKey: 'contactID', joinTableName: 'userHasContacts'} );

但是好像这样不行，已经2个小时了，我正在尝试几种方法来写这个关系......

But it seems like it doesn't work this way, and it's been 2 hours I am trying several ways to write this relation...

唯一对我有用的是

Users.hasMany(UserHasContacts, {foreignKey: 'contactID', joinTableName: 'userHasContacts'} );

UserHasContacts.findAndCountAll({ where: {userID: id} }).success( function(result) {        
    res.json(result);
});

但是我无法在我的查找查询中加入 users 表(通过 急切加载)，它只是返回 userHasContacts 中的数据.

But then I cannot join users table in my find query (via Eager loading) and it simply returns the data inside userHasContacts.

如果有人得到提示，欢迎您！
提前致谢！

If anyone got an hint, you are welcome!
Thanks by advance !

推荐答案

由于你要做的是自关联，你只需要调用 hasMany 一次，这将创建一个联结表

Since what you are trying to do is a self association you only need to call hasMany once, which will create a junction table

User.hasMany(User, { as: 'Contacts', joinTableName: 'userHasContacts'})

这将创建 userHasContacts 表:

Which will create the userHasContacts table as:

CREATE TABLE IF NOT EXISTS `userHasContacts` (`userId` INTEGER , `ContactsId` INTEGER , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`userId`,`ContactsId`)) ENGINE=InnoDB;

要查找用户及其联系人，您可以执行以下操作:

To find users and their contacts you can then do:

User.find({ where: ..., include: [{model: User, as: 'Contacts'}]})

相关文章