bytama

· 2 min read

Count and order by a related table in Sequelize

A correlated subquery in attributes, then order by its alias — no group by, no N+1.

You want categories with how many events each one holds, sorted by that count. The intuitive move is include plus group, which changes the shape of the result and breaks limit. A correlated subquery keeps the query flat.

The query

const categories = await CategoryEvent.findAll({
  attributes: {
    include: [
      [
        sequelize.literal(
          '(SELECT COUNT(*) FROM "Events" WHERE "Events"."categoryId" = "CategoryEvent"."_id")::integer'
        ),
        'totalEvent',
      ],
    ],
  },
  order: [[sequelize.literal('"totalEvent"'), 'DESC']],
  limit,
  offset,
});

Three details make or break it:

  • The subquery is raw SQL, so the table and column names must match what Postgres sees. Sequelize quotes identifiers, and "Events" is case-sensitive.
  • ::integer matters. COUNT(*) returns bigint, and pg hands bigint back as a string; the sort then happens alphabetically (10 before 9).
  • The alias is referenced with quotes in order. In a SELECT, Postgres lets you order by alias, but Sequelize will not quote it for you.

Selecting the counts separately

If the extra attribute is not worth a raw fragment, two queries are honest and cheap:

const rows = await sequelize.query(
  `SELECT "categoryId", COUNT(*)::int AS total
     FROM "Events"
    GROUP BY "categoryId"`,
  { type: QueryTypes.SELECT }
);
const counts = new Map(rows.map((r) => [r.categoryId, r.total]));

One round trip instead of N, and the intent is visible in the SQL.

The version from the docs

The Substack answer this note came from uses the same pattern with Posts and User:

User.findAll({
  attributes: [
    'User.username',
    [sequelize.literal('(SELECT COUNT(*) FROM Posts WHERE Posts.userId = User.id)'), 'PostCount'],
  ],
  order: [[sequelize.literal('PostCount'), 'DESC']],
});

Identical idea, but note the missing cast: with a large Posts table the string comparison shows up as a wrong order, not as an error.

When the count gets used everywhere

A subquery runs per row. If the list is long or the count is on a hot path, denormalize it — a totalEvent column maintained by a trigger — and index what you sort by.

Source: Stack Overflow — Sequelize order by count of association.