sequelize not Include all children if any one match [英] sequelize not Include all children if any one matches
问题描述
我有三个背靠背的关联表.这意味着 item_level_1 有很多 item_level_2，而 item_level_2 有很多 item_level_3.我使用搜索查询来查找名称包含搜索文本的任何父母或孩子.这意味着如果我键入 abc，那么我需要返回所有包含完整详细信息(父母和孩子)的父母或孩子.但在我的情况下，如果 item_level_3 的名称中有 abc，它会返回父级详细信息，但它只返回 item_level_3 中带有 abc 的特定子级.我需要返回 item_level_3 内的所有子级，其中相同的父级.
I am having three association tables back to back. That means item_level_1 have many item_level_2 and item_level_2 have many item_level_3. I used a search query to find any parent or child having a name containing the search text. That means if I type abc, then I need to return all parent or child with full details(parents and children). But in my case, if item_level_3 has abc in the name, it returns the parent details, but it just only returns the specific child with abc from item_level_3. I need to return all children inside item_level_3 where the same parent.
我在 AWS 中使用带有节点的 MySQL 数据库
I am using MySQL database in AWS with node
我检查了 https://sequelize.org/master/manual/eager-loading.html#complex-where-clauses-at-the-top-level 并尝试了不同的组合.但无济于事.我可能会错过一些东西.但是我找不到.
I checked https://sequelize.org/master/manual/eager-loading.html#complex-where-clauses-at-the-top-level and tried different combinations. But not help. I might miss something. But I cannot find it.
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;
}
推荐答案
这是一个复杂的场景，需要一些解决方法.此外，我还没有测试所有场景，因此很抱歉它可能适用于示例案例，但不能满足您的所有需求.不过，我希望这会给你一些指导.
This is a complex scenario and required some workaround. Also, I haven't tested all scenarios, so apologies that it may work for a sample case but not all of your needs. I hope this will give you some directions, though.
基于这里写的SQL，https://dba.stackexchange.com/a/140006，可以在 item_level_2 和 item_level_3 之间创建 2 个 JOIN，1 个用于过滤，1 个用于获取所有关联记录.
Based on the SQL written here, https://dba.stackexchange.com/a/140006, you can create 2 JOINs between item_level_2 and item_level_3, 1 for filtering and 1 for fetching all associated records.
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.
This covers a scenario when 1 item is matching from item_level_3 and there are multiple items that associated with the same item_level_2. This won't work if there are multiple item_level_2 is associated with item_level_1 and 1 of the item_level_2 is matching with the searchText.
我还没有测试过，但是如果你需要的话，也许你也可以为 item_level_1 做类似的事情.
I haven't tested, however, maybe you can do similar thing for item_level_1 as well if you needed.
==================================================
=================================================
更新:
如果 item_level_2 和 item_level_3 的关联为 belongsTo，则上述方案无效.
If the association between item_level_2 and item_level_3 is belongsTo, above solution won't work.
您需要 (省略了错误的解决方案.)WHERE EXISTS 查询 item_level_3.
You'll need
(Omitted the wrong solution.)WHERE EXISTS query for item_level_3.
==================================================
=================================================
更新2:
使用内联 IN 查询进行 item_level_3 文本匹配.
Use inline IN query for item_level_3 text matching.
在进行内联查询之前，请务必转义稍后将进入 Sequelize.literal 的动态内容.
Before making inline query, make sure to escape the dynamic contents that will go into the Sequelize.literal later.
重要提示:由于 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 的 id，其中 searchText 出现在任何子项 (item_level_3) 中.为此，我只查询 item_level_2 和 item_level_3 表并使用 GROUP 和 HAVING.
First setup inline query options to extract item_level_1's ids where the searchText appears in any children (item_level_3). To do so, I query only item_level_2 and item_level_3 tables and using GROUP and 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 的 id，其中任何子级位于item_level_3 有 searchText.
With grouping with item_level_1's id and filtering with HAVING, this will return all item_level_1's id where any of its children at item_level_3 has the searchText.
这仍然只搜索 item_level_3 的名称.
This is still ONLY searching at item_level_3's name.
接下来，将选项转换为内联查询.
Next, translate the options into inline query.
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
最后，将这个生成的查询插入到主查询中.
Finally, insert this generated query into main query.
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,
}]
}]
});
这篇关于sequelize not Include all children if any one match的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
