In MySQL I have the following object table:
| id | parent_id | name |
|---:|----------:|:-----|
| 1 | NULL | root |
| 2 | 9 | obj1 |
| 3 | 10 | obj2 |
| 4 | 7 | obj3 |
| 5 | 8 | obj4 |
| 6 | 4 | obj5 |
| 7 | 2 | obj6 |
| 8 | 3 | obj7 |
| 9 | 1 | obj8 |
| 10 | 1 | obj9 |
And the following Query to get a path from my element (id 6) to root:
SELECT @id :=
(
SELECT parent_id
FROM object
WHERE id = @id
) AS tree
FROM (
SELECT @id := 6
) a
STRAIGHT_JOIN object
WHERE @id IS NOT NULL
The result is:
| tree |
|-----:|
| 4 |
| 7 |
| 2 |
| 9 |
| 1 |
| NULL |
But I need a counter in the result, that represents the travertion through the path (like a counter in a for-loop):
| tree | ctr |
|-----:|----:|
| 4 | 1 |
| 7 | 2 |
| 2 | 3 |
| 9 | 4 |
| 1 | 5 |
| NULL | 6 |
Is it possible to add the counter and how?