Parent left and Parent Right in openerp

Question

What is Parent left and Parent right?

How it works in Openerp?

Thanks

Answer 1

These are the fields for Nested Set Model. to manage hierarchical data structures with unbound depth in SQL. The article Managing Hierarchical Data in MySQL explains this model details with examples in SQL.

OpenERP uses Nested Set Model to manage trees like chart of accounts and warehouse locations. parent_left and parent_right columns are equivalent to right and left columns in the above articles.

Answer 2

As Raphael Collet explains in his answer about OpenERP Server:

parent_left and parent_right are special 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. A and B can be partner categories, for instance. 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

So, imagine that you have six partner categories like below. You can assign parent_left and parent_right by traversing the tree. The result is show in parentheses next to each node. Note that the values there are optimal; in practice, you can leave gaps in the numbers.

• Customers (1, 10)
  • Consumers (2, 3)
  • Partners (4, 9)
    • Basic Partners (5, 6)
    • Gold Partners (7, 8)
• Suppliers (11, 12)

You can retrieve all the subcategories of Customers with a single SQL query. Note that the values 1 and 10 are the parent_left and parent_right of Customers; they can be retrieved as part of the query itself.

    SELECT id FROM partner_category
    WHERE parent_left > 1 AND parent_left < 10

The last remark is that parent_left and parent_right can be updated without traversing 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 new node's ancestors, and one to shift the parent_left and parent_right of the new node's following siblings and their descendants. So parent_left and parent_right can be maintained efficiently.