sequelize not Include all children if any one matches

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 });
        });
    })
}

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 WHERE EXISTS query for item_level_3. (Omitted the wrong solution.)

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

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,
        }]
    }]
});