Getting root node for a given leafs IDs

Question

I got the following tables:

  +-----------------+
  | PRODUCTS        |
  +-----------------+
  | #product_id     |
  | #company_id     |
  +-----------------+
        |     |
+---------------------+
| PRODUCT_BUNDLES     |
+---------------------+
| #parent_product_id  |
| #child_product_id   |
| #company_id         |
+---------------------+

And here is a result in a tree like of the relationships among ids in the PRODUCT_BUNDLES table for some product:

        p1
       /   \
      p2   p3
          / | \
        p4  p5 p6

A user give me the following ids p2, p4, p5 and p6 (the leafs of the tree) and I want return the p1 id (the root of the tree).

How I build select query in mysql to do that?

For now I only have the inverse query. From a root ID give the descendent's IDs.

SELECT bundles.product_id FROM products_bundles bom INNER JOIN products products ON products.product_id = bom.parent_product_id INNER JOIN products bundles ON bundles.product_id = bom.child_product_id WHERE products.product_id = SOMEID;

To clarify what I search is a query that return the parent who have all product_id given as descendant not the parent who have some product_id given as descendant. Thanks

Answer 1

With the naive tree pattern (where you just list the immediate parent/child relationships, sometimes called the adjacency list pattern) the this would be done with a recursive CTE in DBs that support them, but mySQL doesn't. If you know that each tree will never be more than a certain depth you can construct a query that looks at all possible depths and picks the last not found like so:

SELECT COALESCE(t5.parent, t4.parent, t3.parent, t2.parent, t1.parent)
FROM   tree_table AS t1
LEFT OUTER JOIN tree_table AS t2 ON t2.child=t1.parent
LEFT OUTER JOIN tree_table AS t3 ON t3.child=t2.parent
LEFT OUTER JOIN tree_table AS t4 ON t4.child=t3.parent
LEFT OUTER JOIN tree_table AS t5 ON t5.child=t4.parent
WHERE  t1.child = @given_node_id

This fails if a tree becomes deeper than expected though, and is not terribly efficient. You could also consider using a stored procedure to loop up the tree until you get to the root, which would work more generally but is more limited in its use (the above could be turned into a view which could be used as part of other select statements, a stored procedure could not).

To help with queries like this you could consider altering your data structure to contain some unrolled data. This sometimes feels wrong (breaking normal form and requiring extra work on inserts, updates, and deletes) but can be very helpful. Three options are:

1: Maintain the path to a node in an extra column:

So the record (c="p4", p="p3") becomes (c="p4", p="p3", pa="/p1/p3/p4"). You have to maintain the paths when nodes are added or moved, and to work out the root you need to use a string function to extract the first item in the path.

2: Maintain a closure table containing every relationship between nodes and their ancestors:

This is like your existing table but contains all ancestors for each node with a depth marker so for p4 you would record:

node   ancestor   distance   depth
----------------------------------
p4     p1         2          1
p4     p3         1          2

You may not need both the depth and distance columns depending on the queries you need to support and wither you keep this as well as or instead of the existing structure.

To find the root from any given point just look for the ancestor with a depth of 1. Again you have extra work to do maintaining the structure on insert/update/delete.

Of course this all assumes you have a forest of trees, not just a single tree. If you have a single tree you only have one root it is will be the same for every node.

A reading recommendation that I often give is https://pragprog.com/book/bksqla/sql-antipatterns - that covers dealing with some common tree/forest related problems, including this one, in chapter 2.

Answer 2

A long, long time ago (Oct 24, 2011), in a galaxy far away, someone boldly asked

Find highest level of a hierarchical field: with vs without CTEs

In my answer to that post, I wrote three stored procedures to find specific relationships

• GetParentIDByID
• GetAncestry
• GetFamilyTree

If you use the code in GetAncestry, just pick the last Ancestor in the output.

I have referred others to that post

• Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
• Jul 11, 2013 : Recursive self joins
• Dec 10, 2012 : MySQL: Tree-Hierarchical query

In your particular case, you would run GetAncestry on each node and compile it into a distinct list. If the nodes all belong to one tree, you should get one node (in your case, p1). If the nodes are from different trees (from a forest), your compiled list will have multiple root nodes.

Give it a Try !!!