SQL/Laravel: Get parents in tree data structure

Question

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 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.

Answer 1

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

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

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

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
1377	Adjustment	5	6	1374	1373.1374.1377	0

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

id	name	_lft	_rgt	parent_id	path	is_folder
1374	Glass unit	2	7	1373	1373.1374	1
1376	Installation	3	4	1374	1373.1374.1376	0
1377	Adjustment	5	6	1374	1373.1374.1377	0

fiddle

Answer 2

$query->join('categories as p', function ($join) {
    $join->on(DB::raw("FIND_IN_SET(CAST(p.id AS CHAR) COLLATE utf8mb4_unicode_ci, REPLACE(categories.path COLLATE utf8mb4_unicode_ci, '.', ','))"), '>', DB::raw('0'));
})
    ->addSelect('p.*')
    ->distinct();

This worked in laravel.