CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Priyanka Bondarde
Priyanka Bondarde

Reputation: 19

Get the count of Associated tables using sequelize

In my Case Account is a parent table Account that has many Members, an Account has many Groups, Account has many Roles. So I tried getting a count of each table as follows.

  Account.findAll({
    subQuery: false, 

    where: {id: id},

    attributes: {
        include: ['*',
            [Sequelize.fn("COUNT", Sequelize.col("Members.id")), "usersCount"],
            [Sequelize.fn("COUNT", Sequelize.col("Groups.id")), "groupsCount"],
            [Sequelize.fn("COUNT", Sequelize.col("Roles.id")), "rolesCount"]]
    },
    include: [
        {
            model: Member, attributes: [], where: {accountId: id}
        },
        {
            model: Group, attributes: [], where: {accountId: id}
        },
        {
            model: Role, attributes: [], where: {accountId: id}
        }
    ],
    group: ['Account.id']
})
    .then(data => {
        let result = data.map(item => accountObj(item));
        return next(null, result[0]);
    })
    .catch(err => dbHelper.handleError(err, next));

It's giving me an incorrect count. Also tried various other options I found on stack overflow. But not found any solution for the above query.

Upvotes: 0

Views: 2956

Answers (1)

Anatoly
Anatoly

Reputation: 22803

You either need to separate this query into three ones to count each child model record count separately OR remove include and group options and replace Sequelize.fn with Sequelize.literal with subqueries to count child records.

Account.findAll({
    where: {id: id},
    attributes: {
        include: ['*',
  [Sequelize.literal("(SELECT COUNT(*) from members as m where m.account_id=account.id)", "usersCount"],
  [Sequelize.literal("(SELECT COUNT(*) from groups as g where g.account_id=account.id)", "groupsCount"],
  [Sequelize.literal("(SELECT COUNT(*) from roles as r where r.account_id=account.id)", "rolesCount"],
  ]
    }
})

Upvotes: 1

Related Questions