Using COUNT aggregate function on sequelize adding unwanted id field

Question

I am trying to do a COUNT on related models using sequelize 6.21.6 get total number of jobs under each category.

My model looks like:

models.Sector.hasMany(models.Category, {
   foreignKey: 'sectorId',
   as: 'Categories',
});

models.Category.hasMany(models.Job, {
   foreignKey: 'categoryId',
   as: 'Jobs',
});

I am running this query with COUNT:

const getSectorsCategories = async () => {
  const sectors = await Sector.findAll({
    attributes: [
      'name'
    ],
    include: [
      {
        model: Category,
        as: 'Categories',
        attributes: ['name', 'sectorId',
          [sequelize.fn('COUNT', sequelize.col('Categories.Jobs.id')), 'jobCount']
        ],
        include: [
          {
            model: Job,
            as: 'Jobs',
            attributes: ['title', 'categoryId'],
          },
        ],
      },
    ],
    group: ['Sector.id', 'Categories.id'],
  },);
  return sectors;
};

With the following SQL:

Executing (default): 
SELECT 
  "Sector"."id", 
  "Sector"."name", 
  "Categories"."id" AS "Categories.id", 
  "Categories"."name" AS "Categories.name", 
  "Categories"."sectorId" AS "Categories.sectorId", 
  COUNT("Categories->Jobs"."id") AS "Categories.jobCount", 
  "Categories->Jobs"."id" AS "Categories.Jobs.id", 
  "Categories->Jobs"."title" AS "Categories.Jobs.title", 
  "Categories->Jobs"."categoryId" AS "Categories.Jobs.categoryId" 
FROM 
  "Sectors" AS "Sector" 
  LEFT OUTER JOIN "Categories" AS "Categories" ON "Sector"."id" = "Categories"."sectorId" 
  LEFT OUTER JOIN "Jobs" AS "Categories->Jobs" ON "Categories"."id" = "Categories->Jobs"."categoryId" 
GROUP BY 
  "Sector"."id", 
  "Categories"."id", 
  "Categories->Jobs"."id";

You notice this field was added by sequelize automatically: "Categories->Jobs"."id" AS "Categories.Jobs.id"

Which now produces this error:

"error": "column \"Categories->Jobs.id\" must appear in the GROUP BY clause or be used in an aggregate function"

Seems the only way to remove this error is by passing in an empty attributes array to Jobs:

include: [
   {
     model: Job,
     as: 'Jobs',
     attributes: [],
   },
]

Now the aggregate function COUNT works as expected but I don't have any list of job attributes as I wanted.

Is there any workaround for this all-or-nothing approach?

userd3pc · Answer

You can try using window function.

const sectors = await Sector.findAll({
  attributes: [
    'name'
  ],
  include: [
    {
      model: Category,
      as: 'Categories',
      attributes: ['name', 'sectorId',
        [sequelize.literal('COUNT("Categories->Jobs"."id") OVER (PARTITION BY "Sector"."id", "Categories"."id")'), 'jobCount']
      ],
      include: [
        {
          model: Job,
          as: 'Jobs',
          attributes: ['title', 'categoryId'],
        },
      ],
    },
  ],
});

Using COUNT aggregate function on sequelize adding unwanted id field

1 Answers