MySQL query to order by parent then child places

Question

I have a table of pages in my database, each page can have a parent as below:

id            parent_id            title
1             0                    Home
2             0                    Sitemap
3             0                    Products
4             3                    Product 1
5             3                    Product 2
6             4                    Product 1 Review Page

What would be the best MySQL query to select all pages ordered by parent then child then child again if there is more than one level, there will be a maximum of three levels. The above example would produce the desired order:

Home
Sitemap
Products
    Product 1
        Product 1 Review Page
    Product 2

Answer 1

Ugh. Queries like this involving trees are annoying and generally, if you want it to be scalable to any number of levels, you won't do it with a single query, you'll use a few building up the tree at each level.

Answer 2

If you have some control over the table schema, you may want to consider using a nested set representation instead. Mike Hillyer wrote an article on this:

Managing Hierarchical Data in MySQL

Answer 3

I think you should put one more field in your table, called level and store in it the level of the node, and then sort your query by level then by parent.

Answer 4

Well, you can always get it all in one query and process it in PHP. That would be probably the simplier way to get a tree.

Answer 5

If you have to stick with your model, i suggest this query:

SELECT p.id, p.title, 
       (
        SELECT LPAD(parent.id, 5, '0') 
        FROM page parent 
        WHERE parent.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        WHERE child.id = p.id AND parent.parent_id = 0 

        UNION

        SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0'), '.', LPAD(grandchild.id, 5, '0')) 
        FROM page parent 
        INNER JOIN page child ON (parent.id = child.parent_id) 
        INNER JOIN page grandchild ON (child.id = grandchild.parent_id) 
        WHERE grandchild.id = p.id AND parent.parent_id = 0 
       ) AS level  
FROM page p
ORDER BY level;

Example of result set:

+-----+-------------------------+-------------------+
| id  | title                   | level             |
+-----+-------------------------+-------------------+
|   1 | Home                    | 00001             |
|   2 | Sitemap                 | 00002             |
|   3 | Products                | 00003             |
|   4 | Product 1               | 00003.00004       |
|   6 | Product 1 Review Page 1 | 00003.00004.00006 |
| 646 | Product 1 Review Page 2 | 00003.00004.00646 |
|   5 | Product 2               | 00003.00005       |
| 644 | Product 3               | 00003.00644       |
| 645 | Product 4               | 00003.00645       |
+-----+-------------------------+-------------------+
9 rows in set (0.01 sec)

Output of EXPLAIN:

+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
| id   | select_type        | table        | type   | possible_keys | key     | key_len | ref                      | rows | Extra          |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
|  1   | PRIMARY            | p            | ALL    | NULL          | NULL    | NULL    | NULL                     |  441 | Using filesort |
|  2   | DEPENDENT SUBQUERY | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 | Using where    |
|  3   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  3   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
|  4   | DEPENDENT UNION    | grandchild   | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.p.id                 |    1 |                |
|  4   | DEPENDENT UNION    | child        | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.grandchild.parent_id |    1 |                |
|  4   | DEPENDENT UNION    | parent       | eq_ref | PRIMARY,idx1  | PRIMARY | 4       | tmp.child.parent_id      |    1 | Using where    |
| NULL | UNION RESULT       | <union2,3,4> | ALL    | NULL          | NULL    | NULL    | NULL                     | NULL |                |
+------+--------------------+--------------+--------+---------------+---------+---------+--------------------------+------+----------------+
8 rows in set (0.00 sec)

I used this table layout:

CREATE TABLE `page` (
  `id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx1` (`parent_id`)
);

Note that I included an index on parent_id to improve performance.

Answer 6

work smarter not harder:

SELECT menu_name , CONCAT_WS('_', level3, level2, level1) as level FROM (SELECT t1.menu_name as menu_name , t3.sorting AS level3, t2.sorting AS level2, t1.sorting AS level1 FROM en_menu_items as t1 LEFT JOIN en_menu_items as t2 on t1.parent_id = t2.id LEFT JOIN en_menu_items as t3 on t2.parent_id = t3.id ) as depth_table ORDER BY level

that is it..

Ahmad Tarbeya [email protected] http://www.compiatech.com Thx