Databases
StackOverflow2013
Postsdbo
POHow to add a sort order to a MySQL closure table representing a many-to-many relationship?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to add a sort order to a MySQL closure table representing a many-to-many relationship?
    primarykey
    data
    text
    <p>This is a follow-up to my questions here:<br> <a href="https://stackoverflow.com/questions/9691366/how-to-implement-a-many-to-many-hierarchical-structure-in-mysql">How to implement a many-to-many hierarchical structure in MySQL</a><br> and here:<br> <a href="https://stackoverflow.com/questions/9610942/how-to-record-sequential-collections-of-records-in-mysql">How to record sequential collections of records in MySQL</a>.</p> <p>In short, I want to implement in MySQL a table of recipes and another of instructions. A recipe is a sequential series of instructions <strong>or other recipes</strong>. So for example you could imagine a <code>Peach_preserve</code> recipe, and a <code>Peach_tart</code> that uses the <code>Peach_preserve</code>, plus a series of other steps (instructions). <code>Peach_preserve</code> could be used for many other recipes.</p> <p>I read <a href="http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html" rel="nofollow noreferrer">this blog post by Bill Karwin about closure tables</a>, and I think this solution best addresses my challenges (my hierarchy is many-to-many and the steps are sequential). So for example I would have:</p> <pre><code>recipe id name 1 Peach preserve 2 Cubed peeled peaches 3 Fresh peaches 4 Powdered sugar 5 Cook together 6 Peel and cut in chunks 7 Mix step (or instruction) id desc 1 Cook together 2 Buy peaches 3 Buy sugar 4 Peel and cut in chunks 5 Mix recipe_instruction (Ancestor) (Descendant) recipe_id step_id depth descendant_is_instruction 3 3 0 0 3 2 1 1 4 4 0 0 4 3 1 1 6 6 0 0 6 4 1 1 2 2 0 0 2 3 1 0 2 2 2 1 2 6 1 0 2 4 2 1 (and so on...) </code></pre> <p>I'm not a fan of the <code>descendant_is_instruction</code> flag, but I don't know how else to do that. I suppose I could replace it with a <code>descendant_is_leaf</code> to identify terminal items...</p> <p>The sort order is represented by a table that incorporates all the relationships at a depth of 1: </p> <pre><code>Depth=1 table recipe_id step_id order 3 2 1 4 3 1 6 4 1 2 3 1 2 6 2 </code></pre> <p>I'm simplifying here because in practice I would separate ingredients and instructions, but you get the idea.</p> <p>So, is that a good way to combine both a hierarchical data structure and a notion of step order? Anything I should do to improve / simplify?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJDelage
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to add a sort order to a MySQL closure table representing a many-to-many relationship?
      1. USJDelage
      1. VTBountyStart
    2. VO
      singulars
      1. POHow to add a sort order to a MySQL closure table representing a many-to-many relationship?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to add a sort order to a MySQL closure table representing a many-to-many relationship?
      1. USJarosław Gomułka
      1. VTFavorite
    1. This table or related slice is empty.
 

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