MySQL sorting by 2 columns

I'm trying to create a sort of menu system with the following structure and sort the contents accordingly.

+----+-------+----------+
| id | title | parentid |
+----+-------+----------+
| 1  | a     | 0        |
| 2  | b     | 0        |
| 3  | c     | 0        |
| 4  | aa    | 1        |
| 5  | ab    | 1        |
| 6  | ca    | 3        |
+----+-------+----------+

I would like to sort them like the following however I can not find or come up with a union or a sort by query that will work.

+----+-------+----------+
| id | title | parentid |
+开发者_运维知识库----+-------+----------+
| 1  | a     | 0        |
| 4  | aa    | 1        |
| 5  | ab    | 1        |
| 2  | b     | 0        |
| 3  | c     | 0        |
| 6  | ca    | 3        |
+----+-------+----------+

I think what you're really asking for is to order your tree by parent id.

This is a rather complex thing to do, although we do it at work, so I'll show you what we do. Although we use PostgreSQL, so you'll undoubtedly need to adapt this to MySQL. But hopefully it will point you in the right direction.

We use this data structure for our uids table"

                          Table "public.uids"
   Column   |            Type             |              Modifiers
------------+-----------------------------+-----------------------------
 uid        | integer                     | not null default 
 name       | character varying(64)       | not null
 parent     | integer                     |

And we have an auxiliary table which we unfortunately call uid_cache, which looks like this:

          Table "public.uid_cache"
 Column |   Type   | Modifiers | Description 
--------+----------+-----------+-------------
 uid    | integer  | not null  | 
 parent | integer  | not null  | 
 level  | smallint | not null  | 

This table is kept up-to-date with INSERT/UPDATE/DELETE triggers on the main uids table. The trigger does many other things that are very specific to our application, and it's long and unwieldy, so I won't post it here. But this auxiliary table contains one row for every uid/parent relationship that exists on the system, and level shows how many steps there are between each.

So for instance, if our uids table contains:

 uid | parent 
-----+--------
   0 |       
   1 |      0
   2 |      1
   3 |      1

(uid 0 has no parent, it is the master parent uid)

Then our uid_cache table contains:

 uid | parent | level 
-----+--------+-------
   0 |      0 |     0
   1 |      1 |     0
   1 |      0 |     1
   2 |      2 |     0
   2 |      1 |     1
   2 |      0 |     2
   3 |      3 |     0
   3 |      1 |     1
   3 |      0 |     2
(8 rows)

With this table, it's easy to tell at a glance the "distance" between any two uids. 3's distance from it's parent 0 is 2 steps. 3's distance from it's parent 1 is 1 step. And 3's distance from itself is, well, 0 steps. (You may or may not want to record the uid's relationship with itself, depending on your application--it's useful in our case)

Now, with these two tables, we can create this stored procedure:

CREATE OR REPLACE FUNCTION uid_tree(INTEGER) RETURNS INTEGER[] AS $$
    SELECT ARRAY(SELECT parent FROM uid_cache WHERE uid = $1 ORDER BY level DESC)
$$ LANGUAGE SQL STABLE STRICT;

Then with this function, we can do:

SELECT uid,parent,uid_tree(uid)
FROM uids
ORDER BY uid_tree(uid);

And we get:

 uid | parent | uid_tree 
-----+--------+----------
   0 |        | {0}
   1 |      0 | {0,1}
   2 |      1 | {0,1,2}
   3 |      1 | {0,1,3}
(4 rows)

I'm pretty sure this is the answer you are looking for. I also have a high suspicion it's far more involved than you were hoping for!

Please let me know if I can clarify anything for you.