MySQL - How to access all nodes in level 2 in table

Question

I am creating a database for an e-commerce website which have nested categories and i am using modified preorder traversal algo. My question is how can i access all the nodes in level 2 i.e Articles, Portfolio, Contact

Answer 1

The article does not explicitly tells you how to get all the nodes from one level. But if you read it carefully it tells you how to do more -> get the depth count of each category. Then all you have to do is to filter by that depth.

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft;

EDIT (what is going on):

In order to make use of the lft and rgt columns of the nested_category table we should select the table twice.

SELECT *
FROM nested_category AS node, nested_category AS parent

if you inspect this query, you will find out that for each row in the nested_category we select all the rows again. So what we want now, is to remove all the rows from the first table (the one we called AS node) where they are not child's of their parent. That's why we filter with WHERE node.lft BETWEEN parent.lft AND parent.rgt

I want to mention that this query:

SELECT *
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
ORDER BY node.lft;

is equal to

SELECT *
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
ORDER BY node.lft;

So now we have all child's with their parents + 1 (because of way we filter, every child belong to itself)

+-------------+----------------------+-----+-----+-------------+----------------------+------+------+
| category_id | name                 | lft | rgt | category_id | name                 | lft  | rgt  |
+-------------+----------------------+-----+-----+-------------+----------------------+------+------+
|           1 | ELECTRONICS          |   1 |  20 |           1 | ELECTRONICS          |    1 |   20 |
|           2 | TELEVISIONS          |   2 |   9 |           1 | ELECTRONICS          |    1 |   20 |
|           2 | TELEVISIONS          |   2 |   9 |           2 | TELEVISIONS          |    2 |    9 |
|           3 | TUBE                 |   3 |   4 |           1 | ELECTRONICS          |    1 |   20 |
|           3 | TUBE                 |   3 |   4 |           3 | TUBE                 |    3 |    4 |
|           3 | TUBE                 |   3 |   4 |           2 | TELEVISIONS          |    2 |    9 |
|           4 | LCD                  |   5 |   6 |           2 | TELEVISIONS          |    2 |    9 |
|           4 | LCD                  |   5 |   6 |           1 | ELECTRONICS          |    1 |   20 |
|           4 | LCD                  |   5 |   6 |           4 | LCD                  |    5 |    6 |
|           5 | PLASMA               |   7 |   8 |           1 | ELECTRONICS          |    1 |   20 |
|           5 | PLASMA               |   7 |   8 |           5 | PLASMA               |    7 |    8 |
|           5 | PLASMA               |   7 |   8 |           2 | TELEVISIONS          |    2 |    9 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |           1 | ELECTRONICS          |    1 |   20 |
|           7 | MP3 PLAYERS          |  11 |  14 |           7 | MP3 PLAYERS          |   11 |   14 |
|           7 | MP3 PLAYERS          |  11 |  14 |           1 | ELECTRONICS          |    1 |   20 |
|           7 | MP3 PLAYERS          |  11 |  14 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           8 | FLASH                |  12 |  13 |           1 | ELECTRONICS          |    1 |   20 |
|           8 | FLASH                |  12 |  13 |           8 | FLASH                |   12 |   13 |
|           8 | FLASH                |  12 |  13 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           8 | FLASH                |  12 |  13 |           7 | MP3 PLAYERS          |   11 |   14 |
|           9 | CD PLAYERS           |  15 |  16 |           1 | ELECTRONICS          |    1 |   20 |
|           9 | CD PLAYERS           |  15 |  16 |           9 | CD PLAYERS           |   15 |   16 |
|           9 | CD PLAYERS           |  15 |  16 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|          10 | 2 WAY RADIOS         |  17 |  18 |           1 | ELECTRONICS          |    1 |   20 |
|          10 | 2 WAY RADIOS         |  17 |  18 |          10 | 2 WAY RADIOS         |   17 |   18 |
|          10 | 2 WAY RADIOS         |  17 |  18 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
+-------------+----------------------+-----+-----+-------------+----------------------+------+------+

Next step - get the depth count. In order to do that, we have to group by every child (the example uses GROUP BY node.name but it can also be done on node.category_id and count the number of parents - 1 for each group (COUNT(parent.name) - 1) AS depth (its also fine to use parent.category_id instead)

So doing

SELECT node.*, (COUNT(parent.category_id) - 1) AS depth
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.category_id
ORDER BY node.lft;

we get this

+-------------+----------------------+-----+-----+-------+
| category_id | name                 | lft | rgt | depth |
+-------------+----------------------+-----+-----+-------+
|           1 | ELECTRONICS          |   1 |  20 |     0 |
|           2 | TELEVISIONS          |   2 |   9 |     1 |
|           3 | TUBE                 |   3 |   4 |     2 |
|           4 | LCD                  |   5 |   6 |     2 |
|           5 | PLASMA               |   7 |   8 |     2 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |     1 |
|           7 | MP3 PLAYERS          |  11 |  14 |     2 |
|           8 | FLASH                |  12 |  13 |     3 |
|           9 | CD PLAYERS           |  15 |  16 |     2 |
|          10 | 2 WAY RADIOS         |  17 |  18 |     2 |
+-------------+----------------------+-----+-----+-------+

And now is the final step, to say that we want only these records, who have depth = 1 (HAVING depth = 1. HAVING is used here because it is applied after aggregates (and so it can filter on aggregates))

SELECT node.*, (COUNT(parent.category_id) - 1) AS depth
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.category_id
HAVING depth = 1
ORDER BY node.lft;

+-------------+----------------------+-----+-----+-------+
| category_id | name                 | lft | rgt | depth |
+-------------+----------------------+-----+-----+-------+
|           2 | TELEVISIONS          |   2 |   9 |     1 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |     1 |
+-------------+----------------------+-----+-----+-------+

I hope its more clear now. Again sorry for my bad English if i made some mistakes.