如何在Sequelize中使用LEFT JOIN COUNT并默认返回0？

期望实现的原生SQL #

SELECT a.album_id, a.cup_id, a.title, a.thumbnail, a.created_time, IFNULL(ai.cnt, 0) AS count FROM album AS a LEFT JOIN (SELECT album_id, COUNT(*) AS cnt FROM album_image) AS ai ON a.album_id = ai.album_id WHERE cup_id = "gPz9fLmw";

尝试的代码 #

const albums: Album[] = await Album.findAll({
    where: { cupId: cupId },
    attributes: { include: [[sequelize.fn("IFNULL", sequelize.col("albumImages.count"), 0), "cnt"]] },
    include: {
        model: AlbumImage,
        as: "albumImages",
        attributes: [[sequelize.fn("COUNT", sequelize.col("albumImages.album_id")), "count"]],
        required: false
    }
});

报错信息 #

SequelizeDatabaseError: Unknown column 'albumImages.count' in 'field list'

问题原因 #

原写法错误在于：直接通过include关联AlbumImage并聚合时，Sequelize会生成普通JOIN而非子查询聚合的SQL，且主查询无法直接引用关联表中定义的count别名，导致字段不存在的报错。

解决方法 #

方法一：子查询聚合后左连接 #

通过定义临时模型实现原生SQL中的子查询左连接逻辑：

const albums: Album[] = await Album.findAll({
    where: { cupId: cupId },
    attributes: [
        'album_id', 'cup_id', 'title', 'thumbnail', 'created_time',
        [sequelize.fn('IFNULL', sequelize.col('ai.cnt'), 0), 'count']
    ],
    include: [{
        model: sequelize.define('AlbumImageAggregate', {
            album_id: Sequelize.INTEGER,
            cnt: Sequelize.INTEGER
        }, { timestamps: false }),
        as: 'ai',
        attributes: [],
        required: false,
        on: {
            album_id: sequelize.col('album.album_id')
        },
        from: [
            [sequelize.literal('(SELECT album_id, COUNT(*) AS cnt FROM album_image GROUP BY album_id)'), 'ai']
        ]
    }],
    raw: true
});

方法二：直接在属性中嵌入子查询 #

这种写法更简洁，通过literal在主查询属性中嵌入计数子查询：

const albums: Album[] = await Album.findAll({
    where: { cupId: cupId },
    attributes: [
        'album_id', 'cup_id', 'title', 'thumbnail', 'created_time',
        [sequelize.literal('IFNULL((SELECT COUNT(*) FROM album_image WHERE album_image.album_id = album.album_id), 0)'), 'count']
    ],
    raw: true
});

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