Sequelize get all records if value exists on N:N table-CodePudding

I want to achieve this query with 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)

the expected result are all the users that match with the profile and all theirs groups in which some group should be present

this is my relevant code of my associations layer

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',
});

and the one from the controller

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,
   }]

My principal problem is how to filter by userId in the sequelize subquery like i did in the SQL query example from the begining

CodePudding user response：

You need to move this subquery condition to the User's conditions because you have AND userId = U.id. Something like this:

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,
   }]

You should look at generated SQL query and correct the [User] table alias if needed in AND [UG].userId=[User].id