Odoo Hierarchic Relations

Hierarchic Relations are very useful when representing categories of data and forum posts, these data are stored in tree like structure resulting in a parent child relationship. Odoo also utilizes hierarchical relations to represent the some of the data. Here we will be taking Odoo10 as an example, and also explaining the modifications that are coming in future versions.

Odoo uses lots of hierarchical data such as: product classification, multi-level BOM, tasks in the project and its sub-tasks, and so on. We will take the product category model as an example.

The main challenge of using the hierarchical data in database is to reduce the traversal time between the child nodes. To achieve efficient query execution and to avoiod using recursive search, Odoo uses MPTTModified Preorder Tree Traversal. For this we add two additional fields along with the parent_id field, which are parent_left and parent_right. Both are integer fields which represent the range in which it's child nodes are located. Many2one fields are used to represent the parent_id of this records and an One2many is used for tracking all the child of the parent.

I have divided it into three tiers as follows.

What Is MPTT?

MPTT stands for Modified Preorder Tree Traversal, this is also called NESTED SET MODEL. When querying hierarchical records, the general idea is to start with the root directory and recursively query each subdirectory before you can derive a specific hierarchy. As i said before to improve the efficiency of query we added two extra fields called parent_left and parent_right. Assume their are two nodes A and B. A is parent node of B, then we can picture it as below.

B.parent_right > A.parent_left
B.parent_left > A.parent_left
B.parent_right < A.parent_right
B.parent_left < A.parent_right

The main draw back of this type of architecture is that, storage and modification time. For example if we want to add new child record to the tree then we have to re-calculate the parent_let and parent_right of its ancestors. This is more suitable when you read more than you do write. When adding new record to a MPTT parent_right of all the affected nodes will increase by 2.

For example in the case of Odoo product category we can picture the tree structure as follows. parent_left and parent_right values (arbitrary) are inside bracket, Odoo calculates these values automatically.

ALL All(0, 11) / \ ----Internal (1, 2) Internal Saleable ----Saleable (3, 10) / \ ----Service (6, 7) Service Physical ----Physical (4, 5) ----Software (8, 9)
// NOTE : the upper directory parent_right = parent_right + ( subdirectories * 2 )+ 1

This structure enable us to query a child's ancestor or descendant more efficiently. For example if you want to get all the child of Saleable, you can easily do with the following query.

SELECT * FROM product_category WHERE parent_left BETWEEN 3 AND 10;. This query is more effective than the normal recursive method.

If you want to get all the ancestors of a child node you can use the following query. In this case we know that left value of all the ancestors must be smaller than our child and right value of all ancestors must be greater than our child.

SELECT * FROM product_category WHERE parent_left < 4 AND parent_right > 5;. This will return all the ancestor nodes of Physical category.

Even though the updating the structure is complex. But can be done with less query, you can adapt the parent_left and parent_right with two UPDATE queries: one to make some space between the parent_left and parent_right of the node's ascendants, and one to shift the parent_left and parent_right of all the nodes at the right of the new position. So parent_left and parent_right can be maintained efficiently. For deletion of node doesn't require any change.

This explanation is same as in this launchpad question.

Design MPTT In Odoo

In this part, we will look into the code structure and some useful functions in Odoo that can be used to implement the MPTT. Below code is same as the product.category model in Odoo.

# -*- coding: utf-8 -*- class ProductCategory(models.Model): _name = 'product.category' _description = 'Product Category' _parent_name = 'parent_id' _parent_store = True _parent_order = 'name' _order = 'parent_left' name = fields.Char(string='Name', index=True, translate=True, required=True) parent_id = fields.Many2one('product.category', string="Parent Category", index=True, ondelete='cascade') child_id = fields.One2many('product.category', 'parent_id', string='Child Categories') parent_left = fields.Integer(string='Left Parent', index=True) parent_right = fields.Integer(string='Right Parent', index=True)

In the above code snippet MPTT fields are indexed, to speed up the database operation. _parent_store is to add the hierarchic search support for that model. For example you can use two additional domain operators child_of and parent_of in search function. Function definition of child_of can be found here and for parent_of here.

If you want to recalculate the parent_left and parent_right fields of a model, you can call the _parent_store_compute function.


On Odoo12 onwards we can expect change in this algorithm, instead of MPTT Odoo will use Materialized Path as the new method.

Definition of this new algorithm is very simple instead of parent_left and parent_right, we will use single filed named parent_path - which records the hierarchic path of that node.

An example based on the product category is given below.

node | id | parent_path
Internal | 2 | All/Internal
Saleable | 3 | All/Saleable

Discussion on this can be seen in this pull request. The above mention functions also change based on the new method. Changed function are given below.

Based on this child_of operator will become child.parent_path LIKE (tree.parent_path || '%') conditional query like this string concatenation.

  • parent_store_compute -- computes parent_path of all nodes of the model.
  • parent_store_create -- update the parent_path when creating any new node.
  • parent_store_update -- update the parent_path when writing on any node.

For more information on different hierarchical relationship in database you can check this video. And also, if you check the Odoo repo master branch you can see the function definitions of above methods based on Materialized Path.

Hope you got some idea about hierarchical relations in Odoo. If i am missing anything please let me know in the comments !