MySQL display menu including parent menu

Question

what I want is to display all menu items, while keeping a structure.

This question pretty much covers everything I need MySQL 2 level MENU Query.

The only thing missing is: I want to display the actual menu before displaying its sub menus

Let's say I have this:

parent_id   id  key_i18n    myurl
-----------------------------------
0           1   menu1       menu1
0           2   menu2       menu2
2           3   menu2-1     menu2_1
2           4   menu2-2     menu2_2
0           5   menu3       menu3

key_i18n is a placeholder, which is resolved against a language file in order to get the real value depending on the currently selected language

What I'd like to get is:

menu1,
menu2,
menu2-1,
menu2-2,
menu3

Currently I'm getting:

menu1,
menu2-1,
menu2-2,
menu3

menu-2 is missing, which makes it difficult to output a structured menu, because I have nothing to attach the child-menus to.

My current SQL statement looks like this:

SELECT
    IF (m2.parent_id IS NOT NULL, m1.key_i18n, NULL) AS parent,
    COALESCE(m2.key_i18n,m1.key_i18n) AS key_i18n,
    COALESCE(m2.myurl,m1.myurl) AS myurl,
    COALESCE(m2.sequence,m1.sequence) AS sequence
FROM menu AS m1
LEFT JOIN menu AS m2 ON m2.parent_id = m1.id
WHERE m1.parent_id = 0
ORDER BY m1.sequence, m2.sequence

Please ignore the sequence column, which is basically the display-order for items in case they are appended at a later time.

EDIT I got this, which returns exactly the result I want (given the 'sequence'-column specifies the display order throughout the menu):

SELECT DISTINCT r.submenu, r.key_i18n1, r.myurl, r.sequence FROM (
    SELECT
        IF(m2.id IS NULL, false, m1.key_i18n) AS submenu,
        COALESCE(m2.key_i18n, m1.key_i18n) AS key_i18n1,
        COALESCE(m2.myurl, m1.myurl) AS myurl,
        COALESCE(m2.sequence, m1.sequence) AS sequence
    FROM menu AS m1
    LEFT JOIN menu AS m2 ON m2.parent_id = m1.id
    WHERE m1.parent_id = 0
    UNION
    SELECT
        false AS submenu,
        m3.key_i18n AS key_i18n1,
        m3.myurl AS myurl,
        m3.sequence AS sequence
    FROM menu AS m3
    WHERE m3.parent_id = 0
) AS r
ORDER BY r.sequence

It's annoying, that I have to use 2 selects and union, but the latter select statement provides me with 'menu2' - which is what I need.

franek franek · Accepted Answer · 2014-03-04T17:34:58

for two level menu, trick that can be done is to set parent_id like this:

parent_id   id  key_i18n    myurl
-----------------------------------
1*          1   menu1       menu1
2*          2   menu2       menu2
2           3   menu2-1     menu2_1
2           4   menu2-2     menu2_2
5*          5   menu3       menu3

then just order you query by parent_id,id asc

MySQL display menu including parent menu

1 Answers