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_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.
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_right. Assume their are two nodes
A is parent node of
B, then we can picture it as below.
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.
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
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.
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.
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
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_right fields of a model, you can call the
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_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.
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
Hope you got some idea about hierarchical relations in Odoo. If i am missing anything please let me know in the comments !