Welcome to the part 2 of the “Hierarchical Data in Relational Databases”. Part 2 heavily depends on the Part 1, and it will show you how to do the most common tasks with Symfony 4 and Doctrine using stofDoctrineExtensionsBundle.

If you didn’t, please read the Part 1 before continuing with this article.

Introduction

Working with hierarchical data in a relational database is not easy, convenient or efficient. The adjacency list model is the straightforward way to represent hierarchical data. In this mode each node has a parent id. This model is simple to understand but it is not efficient to work with. For example lets say we have category tree and associated products that can be in any category. It is impossible to fetch, with one efficient query, subtree, all products in a given category which are also found in the given category subcategories , total number of products in every category, categories at certain depth, etc…. In this model usually all categories are loaded from db, tree is created in the client code (PHP for example) and then products are fetched once we know subtree category ids.

In the nested set model categories are represented as nested containers. Beside parent id (which is not necessary, but it is used for convenience), two new fields are introduced: left and right (shown on the containers border).

Using self join and left and right values it is easy and efficient to fetch all the children (subtree), path (parents), leafs, etc … all with one efficient query. Also products table can be joined. All this enables as to work with hierarchical data and associated data in elegant and efficient way. With stofDoctrineExtensionsBundle we can enjoy the benefits of the nested set model without getting our hands dirty with the details of the implementation.

The negative side of nested set model is overhead when inserting and deleting nodes, since left and right values of other nodes needs to be recalculated. So it is best used when you need to read the tree more often then you need to change it.

For more details on nested set model and how to work with it without additional libraries read part 1 of the tutorial.

Installing

Accompanying code for this tutorial is available from https://github.com/dribtech/hierarchical-data-tutorial-part-2 .

Clone the code:

or

Run composer:

$ composer install

Edit .env, enter DBAL connection parameters (username, password, and host, port and db name if needed):

stofDoctrineExtensionsBundle configuration

At the time of writing this article the documentation at symfony.com/doc/current/bundles/StofDoctrineExtensionsBundle is/was a little bit outdated (more targeted to the sf3 instead of sf4) so take a peek but don’t literally use everything from that page.

Open config/packages/doctrine.yaml

The tree extension needs its own entity to do the work . Its mapping needs to be registered in Doctrine.

From app/config/config.yml in StofDoctrineExtensionsBundle documentation page I copied the part regarding the tree mappings:

Open config/packages/stof_doctrine_extensions.yaml

By default the bundle doesn’t attach any listeners. So the extensions needs to be enabled explicitly.

Category entity

Category entity is very similar to the Category entity from github.com/Atlantic18/DoctrineExtensions/blob/v2.4.x/doc/tree.md . The only difference is that I am using column name instead of title and that I created the indexes for the lft, rgt and lvl columns.

Checkout src/Entity/Category.php:

Please note the special annotations @Gedmo.

• @Gedmo\Mapping\Annotation\Tree(type="strategy") class annotation, sets the tree strategy. We are using nested – the same one which was described in part 1.
• @Gedmo\Mapping\Annotation\TreeLeft field used to store left tree value
• @Gedmo\Mapping\Annotation\TreeRight field used to store right tree value
• @Gedmo\Mapping\Annotation\TreeParentparent node
• @Gedmo\Mapping\Annotation\TreeLevel field used to store tree level/depth (so there is no need to calculate it with query as in part 1)
• @Gedmo\Mapping\Annotation\TreeRootroot node (in the same table you can store more independent trees – and each will have its root node – this is something we didn’t bother in part 1)

For full list of tree annotations visit the documentation.

Create database

Run:

$ bin/console doctrine:database:create

$ bin/console doctrine:schema:update --force

Tree demonstration will be done with symfony command in order to keep the tutorial do the point and not get into overhead of creating controllers, templates, etc…

In order to create this category tree:

Open src/Command/AppCreateCategoryCommand.php

The tree can be created without and with repository methods – whatever is more convenient for you.

Without repository function, just create the Category entity and set its parent – the extension will take care of calculation of the left, right and level values – for the new node as well as of existing nodes. Just don’t forget to persist the entities. In the example above Home, Bikes, Components and Wheels & Tyres are created in that way.

Sometimes it might be more convenient to use repository methods. Their names are self explanatory:

• persistAsFirstChild($node)
• persistAsFirstChildOf($node, $parent)
• persistAsLastChild($node)
• persistAsLastChildOf($node, $parent)
• persistAsNextSibling($node)
• persistAsNextSiblingOf($node, $sibling)
• persistAsPrevSibling($node)
• persistAsPrevSiblingOf($node, $sibling)

Also checkout vendor/gedmo/doctrine-extensions/lib/Gedmo/Tree/Entity/Repository/NestedTreeRepository.php.

Execute command:

$ bin/console app:category:create

Then run:

$ bin/console doctrine:query:sql "select * from category"

And compare lft, rgt and lvl values with nested set diagram.

Retrieve the data

Retrieve the full tree (or subtree)

Retrieve “Bikes” category and all its subcategories using repository method childrenHierarchy . childrenHierarchy can return categories (and subcategories) as a array tree or formatted HTML string. For example nested ul and li.

You can use this method to get ready made HTML fragment to be used directly on the web page as a menu, or navigation tree structure, or whatever, and the HTML output is highly customizable. If you need even more flexibility just retrieve the tree in a array and process it as you wish.

Checkout src/Command/AppCategoryTreeCommand.php.

In order to see ouput of various way you can fetch the tree run bin/console app:category:tree :

• Array tree
• HTML (as nested UL)
• Customized HTML
• Array of objects (entities) from the root and from the arbitrary node

First get the node which subcategories we want to retrieve:

To retrieve the data we will use repository method:

• $node – From which node to get the children. If null it will return the whole tree. In the example above we want “Bikes” and its subcategories.
• $direct – If true retrieve only direct children and not all subcategories. In the example above – we wanted all subcategories so $direct was set to false.
• $options – enable and customize HTML output instead of array tree. As shown in the example above it is enough to specify ‘decorate’ => true
• $includeNode – weather to include the $node in the result or not. In the following examples we did included it.

Retrieve the array tree

Children are stored under __children key:

Output:

Retrieve the tree as nested html unordered list

In the $options set decorate to true:

Output:

Customize childrenHierarchy HTML output

childrenHierarchy $options parameter accepts following options:

• decorate – if true retrieve tree as HTML
• nodeDecorator: Closure (or null) use $node as argument and returns decorated item as a string
• rootOpen – string (<ul>) or Closure – start of the branch, closure will get $children as parameter
• rootClose – string (</ul>) – branch close
• childOpen- string (<li>) or Closure – start of the node, closure will get $node as parameter
• childClose – string (‘</li>’) – node close
• childSort – array containing keys: “field” – on which field to sort and “dir” direction of the sort, “asc” or “desc”

Lets customize the output by adding attributes to the <li> and link for the nodes:

Output:

Output the tree as objects (entities) instead of arrays

• With TreeObjectHydrator we can build the tree with objects, so it needs to be registrated as hydrator with Doctrine
• The hydrator requires HINT_INCLUDE_META_COLUMNS as query hint
• For output dump component is used instead of print_r or var_dump in order to avoid circular references.

Tree from root node:

Output:

Tree from arbitrary node:

Output:

Retrieve all parents of the node (and the node itself)

Use repository method getPath().

src/Command/AppCategoryPathCommand.php

Output:

Retrieve leaf nodes (nodes without children)

Use repository method: getLeafs().

src/Command/AppCategoryLeafCommand.php

Run:

Output:

Retrieve the depth of the nodes

stofDoctrineExtensionsBundle already calculates depth of each node and saves it in the db – so no extra work is required. Just retrieve the nodes you want and get the Category::$lvl property.

Joins between the nested set model hierarchical data and its items

Or, in other words, how to work with categories and items that belong to that categories. Sample products table and data:

src/Entity/Product.php

Number of products in each category

Goal: retrieve the number of products from each category, parent categories should display the sum of products from all its subcategories.

Using query builder

Command/AppProductCountCommandQB.php

Run:

$ bin/console app:product:qb-count

Output (after prettifying):

Each category contains the sum of its products and all its children products.

Using DQL

Command/AppProductCountCommandDQL.php

Run:

$ bin/console app:product:dql-count

The output is the same as above.

The query created by Doctrine and executed by MySQL is:

Which is the same query we wrote in Part 1:

Delete a node

Just remove it like any other entity and let the bundle all the work:

Command/AppCategoryDeleteCommand.php

If you want to remind your self how deleting the node works under the hood, please visit Part 1: Delete a node.

Conclusion

This article finishes “Hierarchical Data in Relational Databases” series. We now know how to deal with hierarchical data in the efficient, elegant and smart way. And thanks to the Part 1 – we also have the understanding what is going under the hood when using StofDoctrineExtensionsBundle extension.

References

• Hierarchical Data in Relational Databases: Part 1
• StofDoctrineExtensionsBundle
• Tree – Nestedset behavior extension for Doctrine 2