Laravel Question posted in
The official Laravel Documentation can be found here.

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.

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    $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.


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search