Problem: I am using Sequelize in a Node.js application, and I need to add a new field to my query result. The new field, watchListed
, should be true
if a Company
has more than 1 related watchlist
entries for a given user otherwise it should be false.
But it gives me this error:
** Error in get all companies TypeError: attr[0].includes is not a function at C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1085:76 at Array.map () at PostgresQueryGenerator.escapeAttributes (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:1072:37) at PostgresQueryGenerator.selectQuery (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-generator.js:880:28) at PostgresQueryInterface.select (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\dialects\abstract\query-interface.js:407:59) at Company.findAll (C:\Projects\backend-apis\microservices\core\node_modules\sequelize\lib\model.js:1140:47) at runNextTicks (node:internal/process/task_queues:60:5) at process.processImmediate (node:internal/timers:449:9)
**
I am using Sequelize with Node.js, and here's the code I'm trying to run:
const result = await Company.findAndCountAll({
where: filters,
limit: limit,
offset: skip,
order: order,
include: [
{
model: Watchlist,
as: 'watchlists',
required: false,
where: {
user_id: user.id
},
}
], // Perfrom Join operation with watchlist model and compare logged in user's id
attributes: ['id', 'company_name',
[
fn('IF', fn('COUNT', col('watchlists.id')), {
[Op.gt]: 1
}, true, false), 'watchListed'
]
],
// Tried solution with Sequelize.literal
// attributes: [
// [
// literal(`CASE WHEN COUNT(watchlists.id) > 0 THEN
true ELSE false END`),
// 'watchListed',
// ],
// ],
// group: ['Company.id'],
});
// My company models and associations:
class Company extends Model {
static associate(models) {
// Added the foreign key constraint for companyId
Company.hasMany(models.Watchlist, {
foreignKey: 'companyId',
as: 'watchlists'
});
}
}
Company.init({
id: {
type: DataTypes.STRING,
defaultValue: uuid.v4(),
primaryKey: true,
allowNull: false,
},
company_name: {
type: DataTypes.STRING,
allowNull: false,
}
// ... Other companies attributes
}, {
sequelize,
modelName: 'Company',
tableName: 'companies',
timestamps: true,
createdAt: 'created_at',
updatedAt: 'updated_at',
});
// Watchlist model and associations
class Watchlist extends Model {
static associate(models) {
// Added the foreign key constraint for user_id
Watchlist.belongsTo(models.User, {
foreignKey: 'user_id',
onDelete: 'CASCADE'
});
Watchlist.belongsTo(models.Company, {
foreignKey: 'companyId',
onDelete: 'CASCADE',
onUpdate: 'CASCADE',
});
}
}
Watchlist.init({
id: {
type: DataTypes.STRING,
defaultValue: () => uuid.v4(),
primaryKey: true,
allowNull: false,
},
user_id: {
type: DataTypes.STRING,
allowNull: false,
},
companyId: {
type: DataTypes.STRING,
allowNull: false,
}
}, {
sequelize,
modelName: 'Watchlist',
tableName: 'watchlists',
timestamps: true,
createdAt: 'created_at',
updatedAt: 'updated_at',
});
I have also tried to use parameterized queries by using replacements
, but that hasn’t resolved the issue.
What I’ve Tried:
Using
Sequelize.literal
with raw SQL to add thewatchListed
column.Attempted to use
COUNT(*)
withSequelize.literal
and a conditional expression.Tried parameterizing the query to prevent SQL injection.
Attempted grouping by
Company.id
.
Expected Behavior:
- The query should return the
watchListed
flag astrue
if the count ofwatchlists
for that company exceeds 1 andfalse
otherwise.
Environment:
Sequelize version: 6.37.5
Node.js version: v20.15.0
Database: PostgreSQL