I am facing a particular scenario with my database setup. I have three tables named computers, flags, and computerFlags that establish relationships between them. The structure of the computerFlags table is as follows:
computerName | flagId
computer1 | flag1
computer1 | flag2
computer2 | flag2
computer2 | flag3
The computer3 entry does not have any flags associated with it, therefore it is not listed in the table. Using Sequelize, I am trying to create a query that will count all computers without the "flag3". In the example table above, the desired output would be 2 (for "computer1" and "computer3").
This is the current code snippet I have:
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) }
})();When using the following SQL query, I get a partially correct result:
select count("computerName") from "computerFlags"
group by "computerName"
having '2' != all(array_agg("flagId"))
However, I am unable to replicate this behavior in Sequelize. Also, based on the table provided, the mentioned query would return 1 as 'computer3' is absent from the list.
To execute this functionality using Sequelize, I would ideally like to implement something similar to the following:
having: {
[[Sequelize.fn('array_agg', Sequelize.col('flag.id')), 'flagIds']] : {
[Op.all]: {
[Op.ne]: '2'
}
}
}There are two main issues with this approach:
- I am unable to use
[[Sequelize.fn ...]]as the left operand - The referencing of the flag ID might be incorrect, since it should resemble something like
computer->flags->flagId. When utilizingfindAll,computer->flagsreturns an array of flags where each containsflagId.
I am currently quite perplexed and would greatly appreciate any guidance you can offer.