Sequelize count associations in findAll

user_1226063 0
Viewed 1923

Let's say you have a queues table, agent_queues, and agents table. An agent can be in many queues, a queue can have many agents. Now let's say you're trying to get a list of queues and the number of agents in those queues. I would expect something like the following to work:

queues.findAll({
     include: ['agentQueues'],
     group: ['queues.name', 'queues.matcher', 'queues.id'],
     attributes: [[Sequelize.fn('count', Sequelize.col('agentQueues.id')), 'agentCount']]
  })

Instead it produces something like:

SELECT "queues".*
FROM (SELECT
        "queues"."name",
        "queues"."matcher",
        "queues"."id",
        count("agentQueues"."queueId") AS "agentCount"
      FROM "queues" AS "queues"
      GROUP BY "name", "matcher", "id"
     ) AS "queues" LEFT OUTER JOIN "agent_queues" AS "agentQueues" ON "queues"."id" = "agentQueues"."queueId";

Where both the group by and count are in the subquery as opposed to the main query. What am I doing wrong here?

An ideal query would look something like this:

SELECT name, matcher, count(agent_queues."queueId") as agentCount FROM queues
LEFT OUTER JOIN agent_queues ON "agent_queues"."queueId" = queues.id
GROUP BY name, matcher;

The result I'm looking for is something like this:

[{ name: 'Some Queue', matcher: '1 = 1', agentCount: 2 }]
sequelize.js node.js postgresql javascript
1 Answers

It is a bit old question but this problem is also with new versions of sequelize. My best workaround was using a literal instead of fn and count functions.

This code should generate a sql statement you expect.

queues.findAll({
     group: ['queues.name', 'queues.matcher'],
     attributes: ['name', 'matcher',[literal(`(SELECT count(*) FROM "agentQueues" 
                                WHERE queue."id" = "agentQueues"."queueId"])`, 'agentCount')]]
  })
user_40187880
Related