Sort flat list, group by parents

Question

I have a flat list of pages in a mySQL Table.

ID    | Tab index | Page Name | parent

Pages can be children of another page, specified by the "parent" property.

The whole list is sorted using the "Tab index" int column.

Can anybody think of a way to query this list so that

• Items are ordered by Tabindex

• Items that are children of another item are grouped behind the parent item, and ordered by their tabindices (optional)?

My mySQL knowledge doesn't reach that deep.

This is an existing data structure and can't be changed.

Thanks in advance for any input.

Answer 1

If your pages are only nested 1 level deep you could do ... ORDER BY CASE WHEN parent IS NULL THEN id ELSE parent END, parent, tab_id. To achieve the same result with multiple levels of nesting you'd need recursive queries, which MySQL doesn't have support for.

Answer 2

Assuming columns id, pagename, tabindex, parent_id

select *,
    coalesce(
        tabindex,
        (select p2.tabindex from page as p2 where p2.id = p.parent_id limit 1),
        0
    ) as ti
from page as p
order by coalesce(ti, p.tabindex)
;

will return those columns in "ti" order which is the first non-null value of the following:

1. tabindex
2. the parent's tabindex
3. 0

This lets you leave child tabindexes blank and have them "inherit" from the parent. Also, if you want the default sort value to push to the bottom, you can replace the 0 (third arg in coalesce) with (select max(tabindex) + 1 from page). The only caveat is that if a child tabindex is larger than the following parent, it will appear later in the list.