Hierarchies are represented using relations with itself; each record has a parent record and also has many child records. This Parent-child tree relationships are represented using a Many2one relationship with the same model, so that each record references its parent. The reverse One2many will make it easy for a parent to keep track of its children.

Advance ODOO Developers,
Article help to hack complex issue within ODOO framework

What is MPTT?

Odoo uses MPTT for faster query execution and to avoid using recursive search. MPTT stands for Modified Preorder Tree Traversal which is Nested set model. It significantly improves support for these hierarchical data structures, for faster browsing through tree siblings and for easier searches using the additional child_of operator. To enable these features, we need to set the _parent_store flag attribute and add the helper fields parent_left and parent_right to the model.

Hierarchical data such as product classification, multi-level BOM, tasks in the project and its sub-tasks etc are widely being used in Odoo. Let’s study the example of product_category model.

Parent_left and parent_right are special integer fields that are related to the parent_id field. The purpose of those fields is to make queries within the hierarchy execute efficiently: with Parent_left and parent_right, you can retrieve all the descendants of a node without making recursive queries.

Consider two nodes A and B in the hierarchy. For instance, A and B can be categories. Their integer fields parent_left and parent_right are such that:

 B is a descendant of A in the hierarchy (defined by parent_id)

If and only if:

   A.parent_left < B.parent_left and
    A.parent_left < B.parent_right and 
    B.parent_left < A.parent_right and
    B.parent_right < A.parent_right

For Product Category in odoo, we can have tree structure like below. You can assign parent_left and parent_right by traversing the tree. The result is shown in parentheses next to each node.

NOTE: The upper directory parent_right = parent_right + ( subdirectories * 2 )+ 1

You can retrieve all the subcategories of products with a single SQL query. The values between 0 and 11 are the parent_left and parent_right of Products; they can be retrieved as part of the query.

For example if you want to get all the child of Saleable, you can easily get data with the following query which is more effective than the normal recursive method.

SELECT * FROM product_category WHERE parent_left BETWEEN 3 AND 10;

Also you can get particular ancestors related to any child node with knowledge of its parent_left and parent_right. Left value of all the ancestors will be smaller than its child and right value of all ancestors will be greater than its child. Below query will get you all ancestor nodes of Service category.

SELECT * FROM product_category WHERE parent_left < 6 AND parent_right > 7;

You can update parent_left and parent_right without going through the whole hierarchy. Removing a node does not require any change. For adding a node, 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 other to shift the parent_left and parent_right of all the nodes of the new position.

How MPTT works in Odoo?

Here we will take a look of how MPTT is implemented in Odoo. To make database work fast, MPTT fields are indexed in this code. _parent_store is to add the hierarchic search support for that model. For search operation to work efficiently, additional fields like child_of and parent_ofare being used.

# -*- 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)

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

In this algorithm, a single field parent_path is being used in place of two fields parent_left  and parent_right. Let’s take example of product_category.

node     | id | parent_path

Internal | 2 | All/Internal

Saleable | 3 | All/Saleable

Hope this information helped you to understand knowledge of how hierarchical relationships are achieved in Odoo.