Sequelize子查询引用主表列报错：Unknown column 'customers.id'

问题原因 #

开启raw: true后，Sequelize生成SQL时主表字段不会带表名前缀，再加上关联groups表的内连接（required: true），主表的别名可能被自动修改，导致子查询里硬编码的customers.id无法匹配到实际的表字段或别名，从而触发「Unknown column 'customers.id' in 'where clause'」错误。

解决方案 #

方案1：用Sequelize.col()动态关联主表字段 #

替换子查询中硬编码的customers.id为Sequelize.col('id')，让Sequelize自动处理主表的别名和字段映射：

const customers = await this.repository.findAndCountAll({
    where: condition,
    attributes: {
        include: [
            [
                Sequelize.literal(`(
                    SELECT SUM(amount)
                    FROM transactions
                    WHERE transactions.customer_id = ${Sequelize.col('id')}
                    and transaction_type='PAYOUT'
                )`), 'totalDebit'
            ]
        ]
    },
    limit,
    offset,
    include: {
        association: 'groups',
        where: { id: groupId },
        through: { attributes: [] },
        attributes:[],
        required: true
    },
    order:[['createdAt', 'DESC']],
    raw: true,
});

return customers

方案2：为主表指定明确别名 #

给主表设置as别名，确保子查询里的customers.id能匹配到正确的表：

const customers = await this.repository.findAndCountAll({
    as: 'customers', // 指定主表别名
    where: condition,
    attributes: {
        include: [
            [
                Sequelize.literal(`(
                    SELECT SUM(amount)
                    FROM transactions
                    WHERE transactions.customer_id = customers.id
                    and transaction_type='PAYOUT'
                )`), 'totalDebit'
            ]
        ]
    },
    limit,
    offset,
    include: {
        association: 'groups',
        where: { id: groupId },
        through: { attributes: [] },
        attributes:[],
        required: true
    },
    order:[['createdAt', 'DESC']],
    raw: true,
});

return customers

方案3：关闭raw模式（业务允许的情况下） #

如果不需要返回纯JSON格式的数据，可以关闭raw: true，此时Sequelize会正常处理表别名关联：

const customers = await this.repository.findAndCountAll({
    where: condition,
    attributes: {
        include: [
            [
                Sequelize.literal(`(
                    SELECT SUM(amount)
                    FROM transactions
                    WHERE transactions.customer_id = customers.id
                    and transaction_type='PAYOUT'
                )`), 'totalDebit'
            ]
        ]
    },
    limit,
    offset,
    include: {
        association: 'groups',
        where: { id: groupId },
        through: { attributes: [] },
        attributes:[],
        required: true
    },
    order:[['createdAt', 'DESC']],
    // raw: true, 注释掉该行
});

return customers

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