sequelize.js - sequelize 不包括所有 child ，如果任何一个匹配

Question

我有三个背靠背的关联表。这意味着 item_level_1 有很多 item_level_2，item_level_2 有很多 item_level_3。我使用搜索查询来查找名称中包含搜索文本的任何父项或子项。这意味着如果我键入 abc，那么我需要返回所有父项或子项的完整详细信息(父项和子项)。但在我的例子中，如果 item_level_3 的名称中有 abc，它会返回父级详细信息，但它只会返回 item_level_3 中带有 abc 的特定子项。我需要返回同一父级的 item_level_3 内的所有子级。

我在 AWS 中通过节点使用 MySQL 数据库

我检查了https://sequelize.org/master/manual/eager-loading.html#complex-where-clauses-at-the-top-level并尝试了不同的组合。但无济于事。我可能会错过一些东西。但是我找不到它。

exports.searchItems = (body) => {
    return new Promise((resolve, reject) => {
        let searchText = body.searchText.toLowerCase();
        let limit = body.limit;
        let offset = body.offset;
        
        db.item_level_1.findAndCountAll({
            where: {
                [Sequelize.Op.or]: [
                    Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_1.name")), Sequelize.Op.like, '%' + searchText + '%'),
                    Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.name")), Sequelize.Op.like, '%' + searchText + '%'),
                    Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.item_level_3.name")), Sequelize.Op.like, '%' + searchText + '%'),
                ],

                [Sequelize.Op.and]: [
                    Sequelize.where(Sequelize.col("item_level_1.status"), Sequelize.Op.eq, body.status)
                ]
            },
            offset: offset,
            limit: limit,
            distinct: true,
            subQuery: false,
            attributes: ['id', 'name'],
            include: [
                {
                    model: db.item_level_2,
                    as: 'item_level_2',
                    where: {
                        status: body.status
                    },
                    attributes: ['id', 'name'],
                    required: true,
                    include: [{
                        model: db.item_level_3,
                        as: 'item_level_3',
                        where: {
                            status: body.status
                        },
                        required: false,
                        attributes: ['id', 'name']
                    }]
                }
        ]
        }).then(result => {
            resolve({ [KEY_STATUS]: 1, [KEY_MESSAGE]: "items listed successfully", [KEY_DATA]: result.rows, [KEY_TOTAL_COUNT]: result.count });
        }).catch(error => {
            reject({ [KEY_STATUS]: 0, [KEY_MESSAGE]: "items list failed", [KEY_ERROR]: error });
        });
    })
}

预期结果

{
  "status": 1,
  "message": "Rent items listed successfully",
  "data": [
    {
      "id": 21,
      "name": "this is test parent one",
      "item_level_2": [
        {
          "id": 39,
          "name": "this is second test parent one",
          "item_level_3": {
            "id": 9,
            "name": "this is the child description with abc"
          }
        },
        {
          "id": 40,
          "name": "this is second test parent two",
          "item_level_3": {
            "id": 6,
            "name": "this is the child description with def"
          }
        },
        {
          "id": 41,
          "name": "this is second test parent three",
          "item_level_3": {
            "id": 70,
            "name": "this is the child description with ghi"
          }
        }
      ]
    }
  ],
  "totalCount": 1
}

实际结果

{
  "status": 1,
  "message": "Rent items listed successfully",
  "data": [
    {
      "id": 21,
      "name": "this is test parent one",
      "item_level_2": [
        {
          "id": 39,
          "name": "this is second test parent one",
          "item_level_3": {
            "id": 9,
            "name": "this is the child description with abc"
          }
        }
      ]
    }
  ],
  "totalCount": 1
}

item_level_1 模型

module.exports = (sequelize, DataTypes) => {
    const item_level_1 = sequelize.define("item_level_1", {
        id: { type: INTEGER, primaryKey: true, autoIncrement: true },
        name: { type: STRING },
        status: { type: BOOLEAN, defaultValue: 0 }
    }, {
        timestamps: false,
        freezeTableName: true,
    })
    item_level_1.associate = function (models) {
        item_level_1.hasMany(models.item_level_2, { as: 'item_level_2' });
    };
    return item_level_1;

}

item_level_2 模型

module.exports = (sequelize, DataTypes) => {
    const item_level_2 = sequelize.define("item_level_2", {
        id: { type: INTEGER, primaryKey: true, autoIncrement: true },
        name: { type: STRING },
        status: { type: BOOLEAN, defaultValue: 0 },
        itemLevel2Id: { type: INTEGER },
        itemLevel1Id: { type: INTEGER }
    }, {
        timestamps: false,
        freezeTableName: true,
    })
    item_level_2.associate = function (models) {
        item_level_2.belongsTo(models.item_level_3, { as: 'item_level_3', foreignKey: 'itemLevel2Id' });
    };
    return item_level_2;

}

item_level_2 型号

module.exports = (sequelize, DataTypes) => {
    const item_level_3 = sequelize.define("item_level_3", {
        id: { type: INTEGER, primaryKey: true, autoIncrement: true },
        name: { type: STRING },
        status: { type: BOOLEAN, defaultValue: 0 }
    }, {
        timestamps: false,
        freezeTableName: true,
    })
    return item_level_3;

}

Answer 1

这是一个复杂的场景，需要一些解决方法。另外，我还没有测试所有场景，很抱歉它可能适用于示例案例，但不能满足您的所有需求。不过，我希望这会为您提供一些指导。

基于这里写的SQL，https://dba.stackexchange.com/a/140006 ，您可以在 item_level_2 之间创建 2 个 JOIN和 item_level_3 ，1 个用于过滤，1 个用于获取所有相关记录。

item_level_2.hasMany(item_level_3, { as: 'item_level_3' });
// This extra association will be used only for filtering.
item_level_2.hasMany(item_level_3, { as: 'filter' }); 

然后，

db.item_level_1.findAndCountAll({
    where: {
        [Sequelize.Op.or]: [
             Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_1.name")), Sequelize.Op.like, '%' + searchText + '%'),
             Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.name")), Sequelize.Op.like, '%' + searchText + '%'),
             // Use the filter association to filter data.
             Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.filter.name")), Sequelize.Op.like, '%' + searchText + '%'),
        ],
        ...
        include: [
            {
                model: db.item_level_2,
                as: 'item_level_2',
                where: {
                    status: body.status
                },
                attributes: ['id', 'name'],
                required: true,
                include: [
                    {
                        model: db.item_level_3,
                        as: 'item_level_3',
                        where: {
                            status: body.status
                        },
                        required: false,
                        attributes: ['id', 'name']  // This should fetch all associated data. 
                    },
                    {
                        model: db.item_level_3,
                        as: 'filter',
                        where: {
                            status: body.status
                        },
                        required: false,
                        attributes: []  // Do not fetch any data from this association. This is only for filtering.
                    }
                ]
            }
        ]
    }
})

这涵盖了 1 个项目与 item_level_3 匹配的场景并且有多个项目与相同的 item_level_2 相关联.如果有多个 item_level_2，这将不起作用与 item_level_1 有关和 item_level_2 中的 1 个与 searchText 匹配.

我还没有测试过，但是，也许你可以为 item_level_1 做类似的事情如果您需要，也可以。

============================================= ==

更新:

如果item_level_2之间有关联和 item_level_3是belongsTo ，上述解决方案将不起作用。

您需要 WHERE EXISTS查询 item_level_3 .(省略了错误的解决方案。)

============================================= ==

更新2:

使用内联 IN查询 item_level_3文本匹配。

在进行内联查询之前，确保转义将进入 Sequelize.literal 的动态内容稍后。

Important Note: Since sequelize.literal inserts arbitrary content without escaping to the query, it deserves very special attention since it may be a source of (major) security vulnerabilities. It should not be used on user-generated content.

引用:https://sequelize.org/master/manual/sub-queries.html

const escapedSearchText = sequelize.escape(`%${searchText}%`);

首先设置内联查询选项以提取 item_level_1 searchText 所在的 ID出现在任何 child 中(item_level_3)。为此，我只查询 item_level_2和 item_level_3表格和使用 GROUP和 HAVING .

const inQueryOptions = {
    attributes: ['itemLevel1Id'],  // This attribute name and the one in group could be different for your table.
    include: [{
        attributes: [],
        model: db.item_level_3,
        as: 'item_level_3',
        where: {
            name: {
                [Sequelize.Op.like]: escapedSearchText
            }
        }
    }],
    group: 'itemLevel1Id',
    having: Sequelize.literal('COUNT(*) > 0')
};

用 item_level_1 分组的 ID 和过滤 HAVING , 这将返回所有 item_level_1其任何子项位于 item_level_3 处的 ID有 searchText .

这仍然只搜索 item_level_3的名字。

接下来，将选项转换为内联查询。

const Model = require("sequelize/lib/model");
// This is required when the inline query has `include` options, this 1 line make sure to serialize the query correctly.
Model._validateIncludedElements.bind(db.item_level_2)(inQueryOptions);
  
// Then, pass the query options to queryGenerator.
// slice(0, -1) is to remove the last ";" as I will use this query inline of the main query.
const inQuery = db.sequelize.getQueryInterface().queryGenerator.selectQuery('item_level_2', inQueryOptions, db.item_level_2).slice(0, -1);

生成的inQuery看起来像这样。

SELECT `item_level_2`.`itemLevel1Id` 
FROM `item_level_2` AS `item_level_2` 
INNER JOIN `item_level_3` AS `item_level_3` 
    ON `item_level_2`.`itemLevel3Id` = `item_level_3`.`id` 
    AND `item_level_3`.`name` LIKE '%def%' 
GROUP BY `itemLevel1Id` 
HAVING COUNT(*) > 0

最后，将这个生成的查询插入到主查询中。

db.item_level_1.findAndCountAll({
    subQuery: false,
    distinct: true,
    where: {
        [Op.or]: [
            Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_1.name")), Sequelize.Op.like, '%' + searchText + '%'),
            Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.name")), Sequelize.Op.like, '%' + searchText + '%'),
            {
                id: {
                    // This is where I am inserting the inline query.
                    [Op.in]: Sequelize.literal(`(${inQuery})`)
                }
            }
        ]
    },
    attributes: ['id', 'name'],
    include: [{
        attributes: ['id', 'name'],
        model: db.item_level_2,
        as: 'item_level_2',
        required: true,
        include: [{
            attributes: ['id', 'name'],
            model: db.item_level_3,
            as: 'item_level_3',
            required: false,
        }]
    }]
});