Hello everyone) I use Laravel, MariaDB.
For example, we have a table like this, let’s say categories.
| id | name | _lft | _rgt | parent_id | path | is_folder |
|---|---|---|---|---|---|---|
| 1373 | Windows | 1 | 10 | NULL | 1373 | 1 |
| 1374 | Glass unit | 2 | 7 | 1373 | 1373.1374 | 1 |
| 1375 | Accessories | 8 | 9 | 1373 | 1373.1375 | 1 |
| 1376 | Installation | 3 | 4 | 1374 | 1373.1374.1376 | 0 |
| 1377 | Adjustment | 5 | 6 | 1374 | 1373.1374.1377 | 0 |
The Categories model uses the KalnoyNestedsetNodeTrait 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.