Sorting modified preorder traversal mysql tree

Question

Hey, I've implemented a tree in a mysql table using:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

This is the method where you have a table like:

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

To print the table out like normal, you would just orderby the lft column. Is there any easy way to order it in reverse, or have another column like "cost" where all entries of the same "depth" are ordered by cost?

Thanks

Answer 1

The URL you quote shows how to get a SELECT that gives the depth -- if you nest that into another SELECT, you can order as you prefer. For example:

SELECT thename, thedepth
FROM (
  SELECT node.name AS thename, (COUNT(parent.name) - 1) AS thedepth
  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) plain
ORDER BY thedepth DESC;

Similarly, of course, you could have a node.cost AS thecost as well in the inner SELECT, get it in the outer SELECT, and ORDER BY thedepth DESC, thecost ASC or whatever.

Performance may or may not be decent, but you can really tell only by trying (and EXPLAIN SELECT and adding appropriate indices;-).

If you have a sufficiently-smart DB engine, you don't need the nesting -- you can directly ORDER BY a computed column (as thedepth is here). But I think this solution would work on more/older DB engines/versions.