sql - How to Add a Conditional custom Flag in Sequelize Query?

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 the watchListed column.
Attempted to use COUNT(*) with Sequelize.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 as true if the count of watchlists for that company exceeds 1 and false otherwise.

Environment:

Sequelize version: 6.37.5
Node.js version: v20.15.0
Database: PostgreSQL

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

sql - How to Add a Conditional custom Flag in Sequelize Query? - Stack Overflow

与本文相关的文章

评论列表(0)