SequelizeJS-具有连接表的同一个表上的hasMany到hasMany [英] SequelizeJS - hasMany to hasMany on the same table with a join table

问题描述

我的问题很简单:

我有一个名为 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'} );

但是似乎这种方式行不通，已经两个小时了，我正在尝试几种方法来编写这种关系...

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...

对我来说唯一有效的方法是

The only line that worked for me was

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'}]})

这篇关于SequelizeJS-具有连接表的同一个表上的hasMany到hasMany的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！