我有三张table:
用户表:
---------------
userId | name
---------------
1 | Alice
2 | Bob
3 | Charly
---------------
字符串
项目表:
-------------------------------------
projectId | userId | state | color
-------------------------------------
4 | 1 | active | red
5 | 1 | closed | blue
6 | 2 | closed | green
7 | 3 | active | yellow
-------------------------------------
型
主题表:
--------------------------------
topicId | projectId | topicName
--------------------------------
7 | 4 | D
8 | 4 | e
9 | 5 | D
10 | 6 | D
--------------------------------
型
协会:
User.hasMany(model.Project)
Project.hasMany(model.Topics)
型
我想找到所有的User
s谁:
- 有至少一个
project
,它在条件下连接到某个topic
(其中name='D'
); - 包括处于
active
状态所有projects
;
所以,结果,我想
--------------------------------------
userId | userName | projectId | color
--------------------------------------
1 | Alice | 4 | red
2 | Bob | 6 | green
--------------------------------------
型
就像这样:
const users = await User.findAll({
include: [{
model: Project,
include: [{
model: Topics,
where: { name: 'D' },
attributes: [],
}],
attributes: [],
}, {
model: Project,
where: { status: 'active'},
required: false,
attributes: ['color'],
}]
})
型
我可以解决这个问题,如果我添加一个更多的关联:
User.hasMany(models.Project, { as: 'ProjectTopics' });
型
然后在第4行添加到查询as: ProjectTopics
中。但是我不希望有两个关联到同一个表,它们具有相同的键,只是具有不同的别名。
文档中有一行:
options.include[].through.as
The alias for the join model, in case you want to give it a different name than the default one.
型
你能帮助我了解它是如何工作的,以及我如何使用它来解决我的问题吗?
1条答案
按热度按时间qvk1mo1f1#
在用户模型中User.hasMany(model.Project,{foreignKey:
userId
,as:user
})model.Project.hasMany(User,{foreignKey:“userId“})在项目模型中
Project.hasMany(model.Topics,{foreignKey:“projectId”,as:
project
})model.Topics.hasMany(Project,{foreignKey:“projectId”})第三个关联与用户和项目表无关
假设用户表活动列很容易创建到与用户和项目表相关
//如果活动列仅可用User.hasMany(model.Project,{foreignKey:
active
,as:activeUser
})model.Project.hasMany(User,{foreignKey:active
})下一步
users = await User.findAll({ include:[{model:Project,include:[{ model:Topics,where:{ name:'D' },attributes:[],association:
user
}],attributes:[],},{ model:Project,where:{ status:'active'},required:false,attributes:'color'],association:'project`字符串
})