Sequelize中Include内关联表Where条件引用父表Review.id报错问题

问题原因 #

嵌套关联查询时，直接用col("Reviews.id")无法识别主表列——当前查询层级处于Tags的关联上下文，必须明确指定主表的完整引用路径。

解决方案 #

修改Tags关联的through条件，通过带表别名的列引用或literal来关联主表ID：

方法1：使用带表别名的col #

const { Sequelize, col } = require('sequelize');

let options = {
    include: [
        {
            model: Categories,
            as: "categories",
            attributes: ["id", "name"],
            through: { attributes: [] },
            include: [
                {
                    model: Tags,
                    as: "tags",
                    attributes: ["id", "name"],
                    through: {
                        attributes: [],
                        where: {
                            reviewId: col("`Reviews`.`id`")
                        }
                    },
                },
            ],
        },
    ],
    order: [["updatedAt", "DESC"]]
};

方法2：使用literal #

const { Sequelize, literal } = require('sequelize');

let options = {
    include: [
        {
            model: Categories,
            as: "categories",
            attributes: ["id", "name"],
            through: { attributes: [] },
            include: [
                {
                    model: Tags,
                    as: "tags",
                    attributes: ["id", "name"],
                    through: {
                        attributes: [],
                        where: {
                            reviewId: literal("`Reviews`.`id`")
                        }
                    },
                },
            ],
        },
    ],
    order: [["updatedAt", "DESC"]]
};

额外检查点 #

• 确认Tags与Reviews的关联中间表（through表）确实包含reviewId字段
• 验证模型关联定义正确性：Categories和Tags的关联必须基于包含reviewId的中间表建立

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