SQL/Laravel: Get parents in tree data structure

Hello everyone) I use Laravel, MariaDB.

For example, we have a table like this, let's say categories.

The Categories model uses the Kalnoy\Nestedset\NodeTrait trait. In the code below, the query builder already contains basic filters (for example, by name)

if (!empty($filters['recursiveSearch']) && CommonService::parseBoolean($filters['recursiveSearch']) === true) {
/** @var QueryBuilder $query */
}

When passing the recursiveSearch parameter, it is necessary to output not only the values ​​that match the filter, but also all their parents.

For example: by the filter name=adjust we get the string Adjustment. with recursiveSearch you also need to get Windows and Glass unit.

The query can also have a filter parentId. If parentId=null&name=adjust&recursiveSearch=true then it should return Windows

Please help :)

Initially I thought to do just with('ancestors'), but the result gets into the relations, and should be in the main query.

Then I made an additional query, got the result of the first selection, got pluck('ancestors') and already substituted their IDs into the resulting query. It worked, but if in the initial selection there are, for example, 1000 records and each has 3 parents, then in the end there will be where on 3000 IDs.

As an idea, without consider Laravel, you can do this without recursion, since you have the full path for each row.

select p.*
from categories c
inner join categories p on
    find_in_set(cast(p.id as char),replace(c.path,'.',','))>0
where c.id=1377

fiddle

Simpler do this by 2 queries

1. Take path for desired id

select path from categories where id=1377

Output is '1373.1374.1377'.

2.Query all parents with parameter '1373,1374,1377'

select *
from categories
where  find_in_set(cast(id as char),'1373,1374,1377')>0

Request for id and all childrens

select *
from categories c
where  find_in_set(cast(1374 as char),replace(c.path,'.',','))>0

fiddle