StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you can create a MySQL User Defined Function, then you can dynamically create your family tree at execution time using something like the following:</p> <pre><code>DELIMITER // CREATE FUNCTION fnFamilyTree ( id INT ) RETURNS TEXT BEGIN SET @tree = ''; SET @qid = id; WHILE (@qid > 0) DO SELECT IFNULL(r.parent_id,-1),m.ordr INTO @pid,@ordr FROM Relations r JOIN Menu m ON m.id = r.id WHERE r.id = @qid LIMIT 1; SET @tree = CONCAT(@ordr,' ',@tree); SET @qid = @pid; END WHILE; RETURN RTRIM(@tree); END// DELIMITER ; </code></pre> <p>Then the following SQL should give you the sequence you are seeking:</p> <pre><code>SELECT m.id ,m.name ,r.parent_id ,fnFamilyTree( r.id ) FROM Relations r JOIN Menu m ON m.id = r.menu_id ORDER BY fnFamilyTree( r.id ) ; </code></pre> <p>Try it at <a href="http://sqlfiddle.com/#!2/199c25/1" rel="nofollow">http://sqlfiddle.com/#!2/199c25/1</a>. Results are:</p> <pre><code>ID NAME PARENT_ID FNFAMILYTREE( R.ID ) 1 Father (null) 0 3 Son 1 0 0 4 Child 3 0 0 1 5 Granson 4 0 0 1 2 2 Father (null) 1 </code></pre> <p>At least, I think this is what you're after.</p> <p><strong>Update for actual schema</strong></p> <p>User Defined Function:</p> <pre><code>DELIMITER // CREATE FUNCTION fnFamilyTree ( id INT ) RETURNS TEXT BEGIN SET @tree = ''; SET @qid = id; WHILE (@qid > 0) DO SELECT IFNULL(r.`menu_master_id`,-1),m.`order` INTO @pid,@order FROM `menu_has_menu_master` r JOIN `menu` m ON m.`id` = r.`menu_id` WHERE r.`menu_id` = @qid LIMIT 1; SET @tree = CONCAT(LPAD(@order,5,'0'),' ',@tree); SET @qid = @pid; END WHILE; RETURN RTRIM(@tree); END // DELIMITER ; </code></pre> <p>Query:</p> <pre><code>SELECT m.id ,m.name ,r.menu_master_id ,fnFamilyTree( r.menu_id ) FROM menu_has_menu_master r JOIN menu m ON m.id = r.menu_id ORDER BY fnFamilyTree( r.menu_id ) ; </code></pre> <p>Results at <a href="http://sqlfiddle.com/#!2/cb0384" rel="nofollow">http://sqlfiddle.com/#!2/cb0384</a></p> <pre><code>ID NAME MENU_MASTER_ID FNFAMILYTREE( R.MENU_ID ) 8 Dashboard (null) 00001 6 SeÃ§Ãµes do Site (null) 00002 7 Home 6 00002 00003 14 Catalogos 6 00002 00004 15 Marcas 14 00002 00004 00004 16 Categoria 1 14 00002 00004 00006 9 Arquivos (null) 00007 1 AdministraÃ§Ã£o (null) 00127 3 Usuarios 1 00127 00001 5 SecÃµes do iPocket 1 00127 00001 13 Default Setups 1 00127 00002 4 Logs 1 00127 00003 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload