1
\$\begingroup\$

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
\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ It's even much faster than my solution and I had no idea that i could use variables in mysql. Thanks \$\endgroup\$
    – Marco
    Oct 19, 2023 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.