sequelize not Include all children if any one matches
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.
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' });
Then,
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.
}
]
}
]
}
})
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.
I haven't tested, however, maybe you can do similar thing for item_level_1 as well if you needed.
=================================================
Update:
If the association between item_level_2 and item_level_3 is belongsTo, above solution won't work.
You'll need
(Omitted the wrong solution.)WHERE EXISTS query for item_level_3.
=================================================
Update2:
Use inline IN query for item_level_3 text matching.
Before making inline query, make sure to escape the dynamic contents that will go into the Sequelize.literal later.
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.
ref: https://sequelize.org/master/manual/sub-queries.html
const escapedSearchText = sequelize.escape(`%${searchText}%`);
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')
};
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.
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);
The generated inQuery looks like this.
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,
}]
}]
});
Unfortunately i think a subquery is unavoidable. You need to find lvl_2 ids first from the matching lvl_3 items.
const itemsLevel2 = await db.item_level_2.findAll(
{
attributes: [Sequelize.col("item_level_2.id"), 'id2'],
where:
{[Sequelize.Op.and]: [
Sequelize.where(Sequelize.fn('lower', Sequelize.col("item_level_2.item_level_3.name")), Sequelize.Op.like, '%' + searchText + '%'),
Sequelize.where(Sequelize.col("item_level_2.status"), Sequelize.Op.eq, body.status)
]},
include: [{
model: db.item_level_3,
as: 'item_level_3',
where: {
status: body.status
},
required: true,
attributes: ['name']
}]
}
)
ids = itemsLevel2.map(item => item.id);
And then use the required ids like that:
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.col("item_level_2.id"), Sequelize.Op.in, ids),
],
[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: true,
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 });
});
})
}