StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A good read to start with is <a href="http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql" rel="nofollow">Hierarchical Data In MySQL</a> <em>(which I used to be able to find on the MySQL.com website, arghh)</em></p> <p>Read It?</p> <p>Here's how it could be done with the adjacency list model. But <strong>only for a known fixed amount of nesting</strong> <em>(four nesting levels for this example)</em>.</p> <p>I would find out which of my pages are root pages <em>(of the tree)</em>. Then select only those with a query. Put the <code>LIMIT x,x</code> in this select statement.</p> <p>After that, the following statement: <em>(or something like it)</em></p> <pre><code>string query = " SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name IN('ELECTRONICS', '<some other name>'); "; </code></pre> <p>Could return something like this:</p> <pre><code>+-------------+----------------------+--------------+-------+ | lev1 | lev2 | lev3 | lev4 | +-------------+----------------------+--------------+-------+ | ELECTRONICS | TELEVISIONS | TUBE | NULL | | ELECTRONICS | TELEVISIONS | LCD | NULL | | ELECTRONICS | TELEVISIONS | PLASMA | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL | | etc... | etc... | etc... | | +-------------+----------------------+--------------+-------+ </code></pre> <p>The trick is to use only the root names of the query with limit <em>(or ID's if you want)</em> in the <code>IN()</code> statement of the query.</p> <p>This should perform pretty good still <em>(in theory)</em>.</p> <p>The principle of the above query could also be used to find out how many descendants are in a root of a tree <em>(with a little <code>GROUP BY</code>and <code>COUNT()</code> magic ;)</em> Also, you could find out which of your pages are roots with this principle <em>(though I would save that in the tabledata for performance reasons)</em></p> <p>If you want a <strong>dynamic amount of nesting</strong> <em>(for nearly endless scaling)</em>, implementing a <a href="http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql" rel="nofollow">nested set</a> would be the way to go.</p>

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