CODEWITHSUNDEEP
postgresqlsequelize.js
Priyanka Bondarde
Priyanka Bondarde

Reputation: 19

Sequelize Get counts of associated model

I have Roles Model, Member Model as a parent model, and MemberRole as a child

Role.belongsToMany(models.Member, {foreignKey: 'roleId', through: models.MemberRole}); Role.hasMany(models.MemberRole, {foreignKey: 'roleId', as: 'roleMembers'});

Currently, it is giving count irrespective of Member Status. I want to get Count of memberRoles if Member has Active status

subQuery: false,
    where: {accountId: accountId},
    attributes: {
        include: [
            [Sequelize.literal('COUNT(DISTINCT("roleMembers"."id"))'), 'assignedMemberCount'],
            [Sequelize.literal('COUNT(DISTINCT("roleGroups"."id"))'), 'assignedGroupCount'],
        ]
    },
    include: [{
        model: MemberRole,
        as: 'roleMembers',
        attributes: [],
       
       required: false
    },
    order: [['name', 'ASC']],
    group: ['Role.id'],
    limit: options.limitCount,
    offset: options.skipCount

Upvotes: 0

Views: 29

Answers (1)

Anatoly
Anatoly

Reputation: 22803

The most correct way would be to use subqueries especially when you use limit and offset for the main model:

subQuery: false,
where: {accountId: accountId},
attributes: {
  include: [
    [Sequelize.literal('SELECT COUNT("roleMembers"."id") FROM roleMembers where roleMembers.roleId=roles.id'), 'assignedMemberCount'],
    ]
},
order: [['name', 'ASC']],
limit: options.limitCount,
offset: options.skipCount

I didn't indicate assignedGroupCount because I didn't see include for roleGroups. You can add it using the assignedMemberCount subquery as an example.

Upvotes: 1

Related Questions