Nested Set Model Query Question - Depth and Count of Nodes

Databases
1

Hello,

I’m working on the tutorial at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.

This is the query:


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
ORDER BY node.lft;

This is the result:


name	             depth
ELECTRONICS     0
TELEVISIONS      1
TUBE                 2
LCD                   2
PLASMA              2
PORTABLE ELECTRONICS   1
MP3 PLAYERS      2
FLASH                3
CD PLAYERS        2
2 WAY RADIOS    2

How would I modify the query to find out how many items are at each depth? Below is an example of what I would expect from the results:


Depth      Number
0                 1
1                 2
2                 6
3                 1

Thanks in advance,

Tim

2

Wrap an outer query around your current one as a derived table:


SELECT 
  depth, 
  count(*) as total
FROM
  (
  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
  ) as derived_table
GROUP BY 
  depth
3

Thank you very much!

4

awesome… you’re my go-to guy for nested sets now, guelphdad

5

Look just because I’ve got Celko books sitting on my bookshelf doesn’t mean I can’t learn elsewhere! :slight_smile: Plus the OP already did the hard work.

6

Hi,

The example shows how many items are at each depth starting from the top or depth of zero (Electronics). How would the query be changed if I wanted to start counting from Television instead of Electronics? Essentially, I’d like Television to be considered at depth zero and start counting depth from there.

I tried editing the query but had no success.

Hope my question makes sense!

Thanks,

Tim