개발자 노트

Answering questions asked on the site.

Michael asks:

I was wondering how to implement a hierarchical query in MySQL (using the ancestry chains version) for a single row, such that it picks up the parents (if any) and any children (if any).

The idea is, I want to be able to jump in at any point, provide an Id of some sort, and be able to draw out the entire hierarchy for that Id, both upwards and downwards.

We need to combine two queries here:

1. Original hierarchical query that returns all descendants of a given id (a descendancy chain)
2. A query that would return all ancestors of a given id (an ancestry chain)

An id can have only one parent, that’s why we can employ a linked list technique to build an ancestry chain, like shown in this article:

• Sorting lists

Here’s the query to to this (no functions required):

To combine two queries, we can employ a simple UNION ALL.

The only problem that is left to preserve the correct level, since the ancestry chain query conts level backwards, and the hierarchical query will count it starting from zero.

Let’s create a sample table and see what we get:

Table creation details

Now, let’s try to UNION ALL the queries as is:

We see that the hierarchical order is mangled: the first resultset is upside down, the second one is starting from level = 1.

To fix it, we need to change the code that calculates level a little.

First, we need to reverse the ancestry part.

This can be easily done by sorting it on lvl DESC:

We now have it in correct order but with wrong level values.

Since a level is essentially a rownum here, we can just calculate it as a rownum instead:

We disregard the previously selected level at all, leaving it only inside the inline view qi for ordering purposes.

Now, we need to merge the descendancy tree query, but with levels starting from 6, not from 1.

Since this query will come after the ancestry one, we can use accumulated value of @cl (which we used to calculate level in the previous query) as a seed.

To do that, we can take the level returned by the descendancy tree query, and just add @cl to it.

The only problem it to determine when we should increment @cl and when we should add it to level.

We will just use a boolean field which will help us to tell the sets apart.

Here’s the query to do it: