[SOLVED] Sequelize COUNT() with HAVING clause not aliasing properly in SQL query

Sequelize COUNT() with HAVING clause not aliasing properly in SQL query

I need to find users with all the appropriate tags on a one to many relationship (one user to many tags)

My code:

const tags = [10,15,20];
const users = await User.findAll({
  attributes: ['UserModel.id', [fn('COUNT', 'userTags.id'), 'tag_count']],
  include: { association: User.UserTags, where: { id: tagIds }, attributes: ['id'] },
  group: ['UserModel.id'],
  having: { tag_count: { [Op.eq]: tagIds.length } },
});

This produces the following query:

SELECT 
  "UserModel"."id",
  COUNT('usertags.id') AS "_0", 
  "usertags"."id" AS "_1" 
FROM "users" AS "UserModel" 
  INNER JOIN "userTags" AS "usertags" ON "UserModel"."id" = "usertags"."userId" AND ("usertags"."deletedAt" IS NULL AND "usertags"."id" IN (10, 15, 20)) 
WHERE ("UserModel"."deletedAt" IS NULL) 
GROUP BY "UserModel"."id" 
HAVING "tag_count" = 3;

Which produces the following error: column "tag_count" does not exist

I'm specifying the alias up in my call to sequelize, but it appears to be ignored in favor of _0. I've tried calling literal() in the having clause but that produces the same result, from what I can tell I can't use literal in the attributes section. I'm not sure what I'm doing wrong here. I would prefer not to code the whole query by hand I want to understand how to do it the sequelize way.

FYI: Because this problem is occurring up in a proprietary code base I've changed the names of the models in the code above. The db relationship and the practical implications are the same.

Solution

There doesn't seem to be any reason to introduce the alias in the select clause, since you already know its value up front. You can move it to the having clause directly instead:

const users = await User.findAll({
  attributes: ['UserModel.id'],
  include: { association: User.UserTags, where: { id: tagIds }, attributes: ['id'] },
  group: ['UserModel.id'],
  having: where(fn('count' 'UserModel.id'), { $eq: tagIds.length })
});