Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There are other ways to organize hierarchical data besides the methods shown in Mike Hillyer's blog. I like to use a method I call <em>transitive closure table</em> or closure table for short. In this design, you store every path through the hierarchy, as ancestor/descendant pairs.</p> <pre><code>create table closure ( ancestor int, descendant int, length int, primary key (ancestor,descendant), key (descendant,ancestor) ); insert into closure values (1,1,0), (1,3,1), (1,4,2), (1,5,3), (2,2,0), (3,3,0), (3,4,1), (3,5,2), (4,4,0), (4,5,1), (5,5,0); </code></pre> <p>Note that this set includes even the "paths" of length zero, i.e. a menu item is an "parent" of itself.</p> <p>Now you can join each menu item <code>m</code> to every its set of ancestors <code>a</code>, by joining to paths where <code>m</code> is the descandant. From there, join back to the menu item <code>o</code> which is in the set of ancestors, and you can access the <code>order</code>.</p> <p>Use GROUP_CONCAT() to make a string of "breadcrumbs" from the <code>order</code> of each in the chain of ancestors, and this becomes a string you can sort by to get the menu order you want.</p> <pre><code>SELECT m.*, GROUP_CONCAT(o.`order` ORDER BY a.length DESC) AS breadcrumbs FROM menu AS m INNER JOIN closure AS a ON a.descendant = m.id INNER JOIN menu AS o ON a.ancestor = o.id GROUP BY m.id ORDER BY breadcrumbs; +----+----------+-------+-------------+ | id | name | order | breadcrumbs | +----+----------+-------+-------------+ | 1 | Father1 | 0 | 0 | | 3 | Son | 0 | 0,0 | | 4 | Child | 1 | 0,0,1 | | 5 | Grandson | 2 | 0,0,1,2 | | 2 | Father2 | 1 | 1 | +----+----------+-------+-------------+ </code></pre> <p>Note that the breadcrumbs sort as a string, so if you have some <code>order</code> numbers with 2 or 3 digits, you will get irregular results. Make sure your <code>order</code> numbers all have the same number of digits.</p> <hr> <p>As an alternative, you could simply store the breadcrumbs strings in your original menu table:</p> <pre><code>ALTER TABLE menu ADD COLUMN breadcrumbs VARCHAR(255); UPDATE menu SET breadcrumbs = '0,0,1,2' WHERE id = 5; etc. </code></pre> <p>Then you can do a simpler query:</p> <pre><code>SELECT * FROM menu ORDER BY breadcrumbs; </code></pre> <p>But then it's up to you to manually recalculate all affected breadcrumb strings, if you ever change the order of the menu items.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL Tree ordered by parent and child
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBill Karwin
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt would be ideal to create a new table and create a new column. But this is not the case, the query must be using the tables already exist.
      singulars
      1. PO
      1. USDaniel Arantes Loverde
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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