CODEWITHSUNDEEP
mysql
chocolata
chocolata

Reputation: 3338

Advanced MySQL ORDER BY parent sequence and then by child sequence

I've got a MySQL table like this

+-----+-----------+----------+------------+
| uid |   title   | sequence | parent_uid |
+-----+-----------+----------+------------+
|   1 | Home      |        1 | NULL       |
|   2 | Services  |        2 | NULL       |
|   3 | Faq       |        3 | NULL       |
|   4 | Contact   |        4 | NULL       |
|   6 | Service A |        1 | 2          |
|   5 | Service B |        2 | 2          |
+-----+-----------+----------+------------+

Using only a single MySQL query, no MySQL User Defined Functions, no server-side processing with PHP, no processing with JavaScript, would this be possible? If so, what would be the best practice? This is the desired result

+-----+-----------+----------+------------+
| uid |   title   | sequence | parent_uid |
+-----+-----------+----------+------------+
|   1 | Home      |        1 | NULL       |
|   2 | Services  |        2 | NULL       |
|   6 | Service A |        1 | 2          |
|   5 | Service B |        2 | 2          |
|   3 | Faq       |        3 | NULL       |
|   4 | Contact   |        4 | NULL       |
+-----+-----------+----------+------------+

My table type is MyIsam, so there are limitations to the solutions.

Several answers are here, but I cannot seem to order on the basis of my sequence field. 

SELECT p1.* 
FROM page p1 
LEFT JOIN page p2 ON p2.parent_uid = p1.uid
GROUP BY p1.uid
ORDER BY
    CASE
        WHEN p1.parent_uid IS NULL
        THEN p1.sequence
        ELSE p1.parent_uid
    END,
    p1.parent_uid

Several other examples are here: Ordering MySQL results to obey parent/child MySQL Tree ordered by parent and child

Can someone assist? Thanks in advance

Upvotes: 0

Views: 715

Answers (1)

DRapp
DRapp

Reputation: 48139

Although the other answer works on the fixed data, it does NOT work should the actual values of the sequence be different... such as changing the sequence for the "Services" parent (null instance) sequence from 2 to 5.

You DO need to do a left-join as you intended, but what you want is a triple order by... first to keep everything grouped by proper parent, then from each parent, keep the parent in the top position, all child entries in secondary, and finally all child entries in their sequential order.

SELECT 
      t1.* 
   FROM 
      t t1 
         LEFT JOIN t t2 
            ON t1.parent_uid = t2.uid
   ORDER BY
      COALESCE( t2.sequence, t1.sequence ),
      case when t2.uid IS NULL then 1 else 2 end,
      t1.sequence

Revised option for SQLFiddle showing Services as a parent's sequence of 5, THEN all children entries

Upvotes: 4

Related Questions