I want to list all the parent categories of a product. I want to use parent left and parent right attribute but I see some operator with the same name in the documentation.
How does they work?
(CONVERTED AND EXPANDED BRETT LEHRER'S COMMENT)
I could be mistaken but I believe the purpose of that is to find all child ids for that parent without a recursive search. I don't know how that would work in the other direction, finding all parents from the child (without recursion).
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, 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.
Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!
About This Community
|Asked: 7/19/13, 7:17 AM|
|Seen: 4062 times|
|Last updated: 3/16/15, 8:10 AM|