mysql - sequelize 嵌套包含 where 子句

Question

我正在使用 sequelize 进行一些过滤。

我当前的表结构:

• 表 1 包含项目(有图像)和用户(不相关)
• Table1 通过 Table1 上的 Table2id 与 Table2 有直接关系(而不是 Table3)
• Table2 通过 Table2 上的 Table3id 与 Table3 有直接关系(而不是 Table4)
• Table3 通过 Table3 上的 Table4id 与 Table4 有直接关系

考虑到我只能使用顶级 where 子句对 Table2 进行过滤，因此我也想对 Table3 和 Table4 进行过滤。

我填写 where 条件的方式只是使用一个基础对象:

var Table2id       = parseInt(req.query.Table2id) || null,
    Table3id       = parseInt(req.query.Table3id) || null,
    Table4id       = parseInt(req.query.Table4id) || null,
    whereCondition = { deleted: 0 }

if (Table2id) { whereCondition['table2id'] = Table2id }
if (Table3id) { whereCondition['table3id'] = Table3id }
if (Table4id) { whereCondition['table4id'] = Table4id }

Table1.findAndCountAll({
        limit: limit,
        offset: offset,
        order: 'created_at DESC',
        where: whereCondition,
        include: [
            {
                model: User,
            }, {
                model: Item,
                include: [
                    {
                        model: Image
                    }
                ]
            }, {
                model: Table2,
                include: [
                    {
                        model: Table3,
                        include: [
                            {
                                model: Table4,
                            }
                        ]
                    }
                ]
            }
        ],
}).then(function (results) { res.json(results) })

我尝试使用一些我发现的技巧，例如 whereCondition['$Table3.table3id$'] = Table3id 但无济于事。

如何过滤嵌套的包含？有没有另一种方法可以构造查询，这样我就不必嵌套包含，但仍保留此数据结构(是否有比我想到的更好的构造方法)？

编辑:所以我希望既能对包含的表进行排序，又能在顶级 where 子句中设置至少一个参数(如 deleted = 0)。

我试过如下修改查询:

var Table2id       = parseInt(req.query.Table2id) || null,
    Table3id       = parseInt(req.query.Table3id) || null,
    Table4id       = parseInt(req.query.Table4id) || null,
    whereCondition = { deleted: 0 },
    extraWhereCondition = {}

if (Table2id) { whereCondition['table2id'] = Table2id } // figured this can be left alone in this particular case (as it works in top-level where clause)
if (Table3id) { extraWhereCondition['table3id'] = Table3id }
if (Table4id) { extraWhereCondition['table4id'] = Table4id }

Table1.findAndCountAll({
        limit: limit,
        offset: offset,
        order: 'created_at DESC',
        where: whereCondition,
        include: [
            {
                model: User,
            }, {
                model: Item,
                include: [
                    {
                        model: Image
                    }
                ]
            }, {
                model: Table2,
                include: [
                    {
                        model: Table3,
                        where: extraWhereCondition,
                        include: [
                            {
                                model: Table4,
                                where: extraWhereCondition,
                            }
                        ]
                    }
                ]
            }
        ],
}).then(function (results) { res.json(results) })

但这给了我一个错误，即 Table2.Table3.Table4.table4id 在字段列表中是未知的。

Answer 1

你只需要将放在哪里，你也可以创建选项对象，然后将它传递给你需要的地方

• 如果需要，在每个include中放入where condition
• 需要真假改变加入规则

When an eager loaded model is filtered using include.where theninclude.required is implicitly set to true. This means that an innerjoin is done returning parent models with any matching children.

• sequelize 将其称为 eager-loading 访问了解更多详情 eager-loading

var Table2 = require("../models/").table2; //and other model that u need

var option = {
  limit: limit,
  offset: offset,
  order: "created_at DESC",
  where: { deleted: 0 },
  include: [
    {
      model: User,
    },
    {
      model: Item,
      required: true,

      include: [
        {
          model: Image,
        },
      ],
    },
    {
      model: Table2,
      include: [
        {
          model: Table3,
          where: { deleted: 0 },
          include: [
            {
              model: Table4,
              where: { deleted: 0 },
            },
          ],
        },
      ],
    },
  ],
};

Table1.findAndCountAll(option).then(function (results) {
  res.json(results);
});