How to detect level in nested MySQL table? [duplicate]

Question

I have simple child-parent relation by saving parent_id. Is it possible to count which level each entry?

My DB looks like this:

As You can see from the image, for example the first item relation is:

• Aston Martin
  • Vanquish
    • 10
  • V8...
  • DB9
  • Rapide

When I selecting Aston Martin I need to know that it is first record, when Vanquish is the second, and so on.

Answer 1

in my opinion structure not normalised, but if need resolve it as is

SELECT 
t1.id, t1.name,
CASE WHEN parent_id = 0 THEN
       1
ELSE
    (SELECT count(id)+1 from Table t2 WHERE t2.parent_id = t1.parent_id and t2.id < t1.id) 
END AS seq_no 
    FROM Table t1

there are many modifications possible, depending from what finally You need, such as if need select seq_no for single name

Answer 2

I don't think you can do it with a simple query. Calculating the nesting level is like visiting a tree, that it is a recursive structure. So some kind of loop (or recursion) is needed. I have thought a possible solution with a function and one with a stored procedure. Here are my test data:

drop table if exists brand ;
create table brand (
  id int unsigned primary key,
  parent_id int unsigned,
  name varchar(100),
  key (parent_id)
);

insert into brand(id, name, parent_id) values 
  (1, 'aston martin', 0),
  (2, 'vanquish', 1),
  (3, 'V8', 1),
  (85, '10', 2),
  (6, 'bentley', 0),
  (7, 'continental', 6);

brand_level() function take an id as argument and goes up on the hierarchy.

drop function if exists brand_level;
delimiter $$
create function brand_level (brand_id int) returns int deterministic
begin
   declare p_id, l int;
   set l = 0; 
   repeat
      set l = l + 1;
      select parent_id into brand_id from brand where id = brand_id;
   until brand_id = 0 or brand_id is NULL end repeat;
   return if(brand_id=0, l, NULL);
end;
$$
delimiter ;

> select *, brand_level(id) from brand;
+----+-----------+--------------+-----------------+
| id | parent_id | name         | brand_level(id) |
+----+-----------+--------------+-----------------+
|  1 |         0 | aston martin |               1 |
|  2 |         1 | vanquish     |               2 |
|  3 |         1 | V8           |               2 |
|  6 |         0 | bentley      |               1 |
|  7 |         6 | continental  |               2 |
| 85 |         2 | 10           |               3 |
+----+-----------+--------------+-----------------+
6 rows in set (0.00 sec)

If you need to calculate the level often it is better to add a column that contains the level.

alter table brand add (l int unsigned NULL default NULL);

Here is a stored procedure to initialise the brand.l field

drop procedure if  exists add_brand_level;
DELIMITER $$
CREATE PROCEDURE add_brand_level()
BEGIN
    declare rc, level int;
    set level = 1;
    update brand set l = level where parent_id = 0;
    select row_count() into rc;
    while rc != 0 do
        set level = level + 1;
        update brand 
        inner join brand parent on parent.id = brand.parent_id and parent.l is not NULL
        set brand.l = level
        where brand.l is NULL;
        select row_count() into rc;
    end while;
END $$
delimiter ;

Here the result:

> call add_brand_level();
> select * from brand;
+----+-----------+--------------+------+
| id | parent_id | name         | l    |
+----+-----------+--------------+------+
|  1 |         0 | aston martin |    1 |
|  2 |         1 | vanquish     |    2 |
|  3 |         1 | V8           |    2 |
|  6 |         0 | bentley      |    1 |
|  7 |         6 | continental  |    2 |
| 85 |         2 | 10           |    3 |
+----+-----------+--------------+------+
6 rows in set (0.00 sec)

Answer 3

I would refer you to exellent Bill Karwin's book 'SQL Antipatterns' https://pragprog.com/book/bksqla/sql-antipatterns . He has a chapter dedicated to implementing tree like structures in SQL databases (he calls them Naive Trees).

Luckily, this chapter is available to download from Pragratic Programmer's website: http://media.pragprog.com/titles/bksqla/trees.pdf but this excerpt doesn't give you the solution to this problem, but Bill Karwin's blog does: http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

The main idea here is to use a separate table Karwin calls a Closure Table. Using the data from another answer here:

drop table if exists brand ;
create table brand (
  id int unsigned primary key,
  name varchar(100)
);

insert into brand(id, name) values 
  (1, 'aston martin'),
  (2, 'vanquish'),
  (3, 'V8'),
  (85, '10'),
  (6, 'bentley'),
  (7, 'continental');

Note that there is no parent_id field here. All the tree node connections are held in another table:

create table brand_closure (
  ancestor int unsigned not null,
  descendant int unsigned not null,
  primary key (ancestor,descendant),
  foreign key fk_ancestor (ancestor) references brand(id),
  foreign key fk_descendant (descendant) references brand(id)
);

This table will hold all connections on all levels between tree nodes:

insert into brand_closure values
  (1,1),(1,2),(1,3),
  (2,2),
  (3,3),
  (6,6),(6,7),(6,85),
  (7,7),(7,85),
  (85,85);

With this set up you can easily query the tree, for the expense of more work when modyfying the table. To get node and it's level:

select b.name, b.id, count(*) as level
  from brand_closure a
  join brand b on b.id = a.descendant
  group by b.name;

+--------------+----+-------+
| name         | id | level |
+--------------+----+-------+
| aston martin |  1 |     1 |
| bentley      |  6 |     1 |
| continental  |  7 |     2 |
| V8           |  3 |     2 |
| vanquish     |  2 |     2 |
| 10           | 85 |     3 |
+--------------+----+-------+