我想用 sequelize 來實作這個查詢
SELECT U.username, G.groupName
FROM [user] U INNER JOIN [userGroup] UG ON (U.id = UG.userId) INNER JOIN [group] G ON (G.id = UG.groupId)
WHERE U.profileId = @profile AND EXISTS (SELECT groupId FROM [userGroup] WHERE groupId = @group AND userId = U.id)
預期的結果是與組態檔匹配的所有用戶以及應該存在某個組的所有組
這是我的關聯層的相關代碼
db.users.belongsToMany(db.group, {
as: 'Groups',
through: UserGroup,
foreignKey: 'userId',
otherKey: 'groupId',
});
db.group.belongsToMany(db.users, {
as: 'Users',
through: UserGroup,
foreignKey: 'groupId',
otherKey: 'userId',
});
和來自控制器的那個
User.findAll({
where: {profileId = Number(profileType)},
include: [{
model: Group,
as: 'Groups',
attributes: ['groupName'],
where: [
{
[Op.and]: sequelize.literal(
`EXISTS (SELECT [UG].groupId FROM [userGroup] AS [UG] WHERE [UG].groupId = ${group})`
),
};
]
required: true,
}]
我的主要問題是如何在 sequelize 子查詢中按 userId 過濾，就像我從一開始在 SQL 查詢示例中所做的那樣
uj5u.com熱心網友回復：
您需要將此子查詢條件移動到用戶的條件，因為您有AND userId = U.id. 像這樣的東西：
User.findAll({
where: {
[Op.and]: [{
profileId = Number(profileType)
},
sequelize.literal(
`EXISTS (SELECT [UG].groupId FROM [userGroup] AS [UG] WHERE [UG].groupId = ${group} AND [UG].userId=[User].id)`
)]
},
include: [{
model: Group,
as: 'Groups',
attributes: ['groupName'],
required: true,
}]
您應該查看生成的 SQL 查詢并在[User]需要時更正表別名AND [UG].userId=[User].id
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422528.html
標籤：
上一篇：UNIONALL與CTE