sql - MySQL Tree ordered by parent and child

Question

I'm trying to display a tree structure in MYSQL with this reference (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) and I'm seeing that only works with 1 parent in same table.
If you have another table to join with, nothing go well. Example:

Table Menu:

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 2  | Father |   1   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
+----+--------+-------+

Table Relations

+----+---------+-----------+
| id | menu_id | parent_id |
| 1  |    1    |    NULL   |
| 2  |    2    |    NULL   |
| 3  |    3    |     1     |
| 4  |    4    |     3     |
| 5  |    5    |     4     |
+----+---------+-----------+

Do the SELECT

SELECT child_menu.*, menu.* FROM menu, relations AS child_menu
WHERE menu.id = child_menu.menu_id
GROUP BY menu_id

I Have this:

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 2  | Father |   1   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
+----+--------+-------+

I'm trying to leave their children in order in the same SELECT.
From what I see in the examples, only works if the parent is in the same table.
Can someone help me? thank you

Edited: EXPECTED OUTPUT:

+----+--------+-------+
| id |  name  | order |
| 1  | Father |   0   |
| 3  | Son    |   0   |
| 4  | Child  |   1   |
| 5  | Granson|   2   |
| 2  | Father |   1   |
+----+--------+-------+

I.E. Father
       Son
         Child
            Grandson

Answer 1

There are other ways to organize hierarchical data besides the methods shown in Mike Hillyer's blog. I like to use a method I call transitive closure table or closure table for short. In this design, you store every path through the hierarchy, as ancestor/descendant pairs.

create table closure (
    ancestor int,
    descendant int,
    length int,
    primary key (ancestor,descendant),
    key (descendant,ancestor)
);
insert into closure values
(1,1,0),
(1,3,1),
(1,4,2),
(1,5,3),
(2,2,0),
(3,3,0),
(3,4,1),
(3,5,2),
(4,4,0),
(4,5,1),
(5,5,0);

Note that this set includes even the "paths" of length zero, i.e. a menu item is an "parent" of itself.

Now you can join each menu item m to every its set of ancestors a, by joining to paths where m is the descandant. From there, join back to the menu item o which is in the set of ancestors, and you can access the order.

Use GROUP_CONCAT() to make a string of "breadcrumbs" from the order of each in the chain of ancestors, and this becomes a string you can sort by to get the menu order you want.

SELECT m.*, GROUP_CONCAT(o.`order` ORDER BY a.length DESC) AS breadcrumbs
FROM menu AS m
INNER JOIN closure AS a ON a.descendant = m.id
INNER JOIN menu AS o ON a.ancestor = o.id
GROUP BY m.id
ORDER BY breadcrumbs;

+----+----------+-------+-------------+
| id | name     | order | breadcrumbs |
+----+----------+-------+-------------+
|  1 | Father1  |     0 | 0           |
|  3 | Son      |     0 | 0,0         |
|  4 | Child    |     1 | 0,0,1       |
|  5 | Grandson |     2 | 0,0,1,2     |
|  2 | Father2  |     1 | 1           |
+----+----------+-------+-------------+

Note that the breadcrumbs sort as a string, so if you have some order numbers with 2 or 3 digits, you will get irregular results. Make sure your order numbers all have the same number of digits.

---

As an alternative, you could simply store the breadcrumbs strings in your original menu table:

ALTER TABLE menu ADD COLUMN breadcrumbs VARCHAR(255);
UPDATE menu SET breadcrumbs = '0,0,1,2' WHERE id = 5;
etc.

Then you can do a simpler query:

SELECT * FROM menu ORDER BY breadcrumbs;

But then it's up to you to manually recalculate all affected breadcrumb strings, if you ever change the order of the menu items.