Sequelize - "array_agg" in a "having clause"

Question

I have the following situation: Table computers, table flags and table computerFlags that links them. Table computerFlags looks like:

computerName | flagId
  computer1  | flag1
  computer1  | flag2
  computer2  | flag2
  computer2  | flag3

computer3 does not have any flags, so it is not in the table I'm working with sequelize, and I'm trying to execute a query to count all computers without "flag3", in the above example table the answer would be 2 ("computer1" and "computer3").

This is the code I have so far:

import { DataTypes, Sequelize, Op } from "sequelize";

(async () => {
    try {
        const postgresDB = new Sequelize('postgres://<my-pg-credentials>>', {
            logging: true,
        });

        const computers = postgresDB.define('computer', {
            name: {
                type: DataTypes.TEXT,
                primaryKey: true
            },
            // Other computer fields
        });

        const flags = postgresDB.define('flag', {
            id: {
                type: DataTypes.TEXT,
                primaryKey: true
            },
            name: DataTypes.TEXT,
        });
        const computerFlags = postgresDB.define('computerFlag', {}, { createdAt: false, updatedAt: false })

        computers.belongsToMany(flags, { through: computerFlags });
        flags.belongsToMany(computers, { through: computerFlags });
        await postgresDB.sync({ alter: true })
        const c = await computers.count({


            distinct: true,
            group: ['computer.name'],
            include: [

                {
                    model: flags

                },
            ]
        });
        } catch (err) { console.log(err) }
})();

I get a half-good result with the next SQL query:

select count("computerName") from "computerFlags"
group by "computerName"
having '2' != all(array_agg("flagId"))

But I can't find a way to produce this in sequelize, and also, for the above table it'll return 1 as 'computer3' is not in the table

To execute this with sequelize, I would like to do something like that:

having: {
  [[Sequelize.fn('array_agg', Sequelize.col('flag.id')), 'flagIds']] : {
    [Op.all]: {
      [Op.ne]: '2'
    }
  }
}

But there are two problems with that:

I cannot use [[Sequelize.fn ...]] as a left operand
I'm not sure if the way I refer to the flag ID is correct as it should be something like computer->flags->flagId. computer->flags returns an array of flags when I use findAll, each one containing flagId.

I'm really lost and confuse and I'd appreciate your help.

Erwin Brandstetter · Answer 1 · 2021-08-13T21:17:13.933

I'm trying to execute a query to count all computers without "flag3"

So use NOT EXISTS. (Some would say, a "semi-anti-join".)

SELECT count(*)
FROM   computers c
WHERE  NOT EXISTS (
   SELECT FROM computerFlags cf
   WHERE  cf.computerName = c.computerName
   AND    cf.flagId = 'flag3'
   );

Only eliminates computers from the count that actually do have an entry with 'flag3'.

Should perform best.

Aside: CaMeL case names are not ideal for Postgres. See:

Are PostgreSQL column names case-sensitive?

Sequelize - "array_agg" in a "having clause"

1 Answers1