如何在Sequelize查询中添加条件自定义字段watchListed？

问题背景 #

在Node.js的Sequelize应用中，需要给Company查询结果添加watchListed字段：当公司对应用户的watchlist条目数>1时为true，否则为false，但运行代码时抛出TypeError: attr[0].includes is not a function错误。

错误信息 #

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)

相关代码 #

尝试的查询代码 #

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
                        },
                    }
                ], // 与watchlist模型关联，匹配当前登录用户ID
                attributes: ['id', 'company_name',
                    [
                        fn('IF', fn('COUNT', col('watchlists.id')), {
                            [Op.gt]: 1
                        }, true, false), 'watchListed'
                    ]
                ],
              // 尝试过用Sequelize.literal的方案
              // attributes: [
                //     [
                //         literal(`CASE WHEN COUNT(watchlists.id) > 0 THEN 
                            true ELSE false END`),
                //         'watchListed',
                //     ],
                // ],
                // group: ['Company.id'],
            });

Company模型及关联 #

class Company extends Model {
    static associate(models) {
        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,
    }
    // 其他字段
}, {
    sequelize,
    modelName: 'Company',
    tableName: 'companies',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
});

Watchlist模型及关联 #

class Watchlist extends Model {
    static associate(models) {
      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',
});

解决方案 #

错误原因是使用fn('IF')的方式不符合Sequelize语法，PostgreSQL需用CASE表达式结合literal，同时必须正确配置分组。

正确的查询代码 #

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
            },
            attributes: [] // 不返回watchlist冗余字段，仅用于统计
        }
    ],
    attributes: [
        'id',
        'company_name',
        [
            sequelize.literal(`CASE WHEN COUNT(watchlists.id) > 1 THEN true ELSE false END`),
            'watchListed'
        ]
    ],
    group: ['Company.id', 'Company.company_name'] // 所有非聚合字段必须加入分组
});

关键说明 #

• 聚合与分组：使用COUNT(watchlists.id)统计条目数时，必须将Company的所有查询字段加入group，否则PostgreSQL会抛出分组错误。
• literal语法：直接用sequelize.literal编写CASE表达式，避免Sequelize语法解析错误。
• 查询优化：设置include的attributes: []，不返回watchlist的无关字段，提升查询效率。

验证效果 #

查询返回的每个Company对象会包含watchListed字段：

• 当用户对该公司的watchlist条目数>1时，值为true
• 否则为false

内容的提问来源于stack exchange，提问作者Anurag Pandey