mySQL adjacency list model : Always retrieve parent and children/siblings

Question

I’m using mySQL to create and adjacency list model type of table. I have only one level, that is a parent with children, like this:

TABLE article (article_id, title, parent_article_id)

Here’s a sample data

article_id title parent_article_id
-------------------------------------
45 abc null
61 mm null
59 hgg 61
62 sef 61
67 wsr 61

Basically, I want to retrieve the parent and children/siblings depending of the where clause.

So, if I select an article_id, let’s say 59, I want to return:

article_id title parent_article_id
-------------------------------------
61 mm null
59 hgg 61
62 sef 61
67 wsr 61

That is the parent/top of the node (61), and chidren/sibings (59, 62, 67)

If i select an article_id , let’s say 61, I want to return:

article_id title parent_article_id
-------------------------------------
61 mm null
59 hgg 61
62 sef 61
67 wsr 61

That is the top of the node(61), and chidren/sibings (59, 62, 67)

If i select a article_id , let’s say 45, I want to return:

article_id title parent_article_id
-------------------------------------
45 abc null

There is no children/siblings, just the top of the node.

I’ve created a query that works, but I wonder if there is a better query to achieve what im looking for.

Here’s my query:

(
 SELECT
 ar.article_id,
 ar.parent_article_id parent_article_id,
 ar.title
 FROM
 article ar
 WHERE
 ar.article_id = 61
)
UNION (
 SELECT
 c.article_id,
 c.parent_article_id parent_article_id,
 c.title
 FROM
 article p
 JOIN article c ON p.parent_article_id = c.parent_article_id
 OR p.parent_article_id = c.article_id
 OR c.parent_article_id = p.article_id
 WHERE
 p.article_id = 61
)
ORDER BY
 parent_article_id

Answer 1

Yes, there is a better query to achieve this, with a subquery masquerading as an user-defined variable.

I always start simple, and first create a root query:

SELECT *
FROM article
WHERE article_id = 59;

This will only give me an article when the article_id itself is 59. This will clearly not do. We also need to find the children of the parent of this article, the siblings. So I first get the parent in a variable:

SET @parent_id := (SELECT parent_article_id
 FROM article
 WHERE article_id = 59);

I use a variable here because it is probably needed again later on. Note that the value of the variable is now 61.

Now we can easily get the siblings like this:

SELECT article_id 
FROM article
WHERE parent_article_id = @parent_id;

This will return articles [59, 62, 67] for article 59.

Looks good, but it is still not complete. Notice that you want a result of [59, 61, 62, 67] for article 59 and now we've only got [59 (twice), 62, 67]. What's missing?

Well, article 61 is missing, or what you call the "top node". It's not difficult to get this now:

SELECT *
FROM article
WHERE article_id = @parent_id;

This will result in 61 and completes our result.

Now I need to put the three queries together, and sort the result:

SET @child_id := 59;
SET @parent_id := (SELECT parent_article_id
 FROM article
 WHERE article_id = @child_id);
SELECT *
FROM article
WHERE parent_article_id IN (@child_id, @parent_id) OR
 article_id IN (@child_id, @parent_id)
ORDER BY article_id;

See: http://sqlfiddle.com/#!9/eddab1d/5

Notice how there's one extra query hidden in there:

SELECT *
FROM article
WHERE parent_article_id = @child_id;

This is for the case when the article is the "top node". In that case you still want the children.

• Marco: It's even much faster than my solution and I had no idea that i could use variables in mysql. Thanks