Creating lft and rgt columns dynamically in MySQL

Question

Can anyone guide me on how to create the lft and rgt columns (parsed from left to right in a tree from root)dynamically for a table using MySQL Query?

The structure of my table looks like this:

id----name----parent_id

and I want the structure like this :

name----lft----rgt

Answer 1

You can self-join the table and aggregate:

select
    t.id
    t.name,
    min(t1.id) left_id,
    max(t1.id) right_id
from mytable t
left join mytable t1 on t1.parent_id = t.id
group by t.id, t.name

This assumes exactly two children per node. If some nodes may have one child only, you can add a little logic:

select
    t.id
    t.name,
    min(t1.id) left_id,
    case when min(t1.id) <> max(t1.id) then max(t1.id) end right_id
from mytable t
left join mytable t1 on t1.parent_id = t.id
group by t.id, t.name

Answer 2

I don't like the nested-set model for representing hierarchical data in a relational database. It was made popular by Joe Celko's 2004 book "Trees and Hierarchies in SQL for Smarties" and by a blog post by Mike Hillyer which dates back at least to 2010.

One problem with the nested-set model is that adding nodes to the hierarchy must be done one node at a time, because every node inserted to the tree requires renumbering the left and right from the point of insertion to the right side of the tree.

A friend suggested to me to use floating-point numbers instead of integers for left and right, then you can just use fractional values in between the existing numbers when you insert a new node to the tree.

Another problem with the nested-set model is that the left and right values aren't references to any other data elements. In fact, the make no sense except in their relationship to the left and right values of other nodes. So there's no data integrity, and it's easy for data anomalies to corrupt your hierarchy.

The only hierarchical model with data integrity is the one you've already got — storing parent_id in each node's row. There's no way for data anomalies to exist when every node knows its own parent, and there will be no argument or conflict with any other rows.

The downside of that model, called the Adjecency List model, is that MySQL doesn't support recursive queries until version 8.0. See examples in https://dev.mysql.com/doc/refman/8.0/en/with.html