OpenERP : Parent_left and parent_right explained!

Guys,

By the time we are typing this down(1 AM crossing soon), Husen and Jay have been delivering Remote Training of the month and this has given us an idea to share a very nice topic which has been a ‘What’ for many.

It has been a busy set of months as have been busy managing Trainings. It is to share that we just finished July 2012 and preparing for the overwhelming response for Aug 2012 OpenERP Training in India.

I understand you might have a hard time understanding what is all about parent_left and parent_right!

Had you seen the code for account.account and product.category, you would better know what it looks like and what’s the importance! Basically, they have been added for faster execution of search call when you have lots of records on these models. In short, you can count them the agents of binary search!

The parent_left and parent_right are 2 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, B.parent_right and B.parent_left, 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 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.


I hope this helps understanding how they work.
Thanks,

Serpent Consulting Services.

Idea : From the questions of many programmers!
Reference : Own study, understanding.
Contents : Thanks to Launchpad for some good contents.

If you face issues on them, having your records imported by CSV:
To fix on your db:
alter table account_account drop parent_left;
alter table account_account drop parent_right;
and restart the server with –update=account.