Threaded data in Yii2

Question

I am looking for a solution through Active Record to get the hierarchical data in single query.

What I am doing now is first fetching all the data and then converting array to desired array with a recursive function.

$allUsers = User::find()
        ->asArray()
        ->all();
$arr = Yii::$app->TreeComponent->getUserChildren($allUsers, $userId, $userId);

and in TreeComponent

public function getUserChildren($src_arr, $currentId, $userId, $parentFound = false)
{
    $cats = array();
    foreach ($src_arr as $row) {
        if ($row['id'] == $userId) {
            $row['parent'] = "";
        }
        if ((!$parentFound && $row['id'] == $currentId) || $row['parent'] == $currentId) {
            $rowData = array();
            foreach ($row as $k => $v)
                $rowData[$k] = $v;
            $cats[] = $rowData;
            if ($row['parent'] == $currentId) {
                $cats = array_merge($cats, $this->fetchRecursive($src_arr, $row['id'], true));
            }
        }
    }
    return $cats;
}

Its working fine. But recently I go through CakePHP's find('threaded'), I think it will save recursive function execution time and less code.

I am curious if there is any function exists in Active Record too.

Answer 1

In Yii2 there is no such functionality. CakePHP will also do multiple queries in the background and just wrap it in a multi-threaded call. The main speed-advantage you would gain if you could actually only perform one single query.

There are two ways to achieve this:

• Your way...fetching everything and merge it within your code
• NestedSet pattern as mentioned in a comment above

NestedSet

For Yii2 there us a very good extension implementing the nested set pattern. You can find it here:

https://github.com/creocoder/yii2-nested-sets

Pros and Cons

The advantage is - obviously - that you can fetch everything with a single query. You can even save multiple trees in one table.

The main disadvantage is sorting according to columns while still maintaining the tree structure. The nested set is organised with two attributes: left and right. This means, that all the data is sorted according to these two attributes. To fetch a tree-structure sorted eg by name, you'll still have to implement a code-side functionality to alter the received data-set after the query. The cleanest way is to sort the data while saving...meaning insert a new record according to its place in your desired sorting

Wikipedia has a very good explenation of the nested set: https://en.wikipedia.org/wiki/Nested_set_model

This illustration shows how it works:

Example: 'slacks' and 'jackets' are children of 'suits' as both their left and right attributes are between the left (3) and right (8) value of 'suits'. If you wanted to add a child to 'jackets' you would inject it between 6 and 7...therefore increasing all values higher or equal than 7 by two. The newly injected child of 'jackets' would then receive a left value of 7 and a right value of 8.

As you can see, you can now easily fetch a whole (sub-)tree simply by filtering the left and right attributes. I f you wanted everything from 'suits' downwards your query would look something like this:

SELECT * FROM mytable WHERE left >= 3 AND right <= 8 ORDER BY left ASC

Final Answer to your question

If your main focus is comfort

No. There is no such functionality. If you still want a regular tree in your db and just don't want to care about merging the data, you would have to write a similliar functionality to CakePHPs methods. This should be quite easy and I think there would be a lot of people interested in it.

If your main concern is speed

Use the nested set. It's one hell of a pattern and sooo powerful!