CODEWITHSUNDEEP
mysqlsqlrecursive-query
qwaz
qwaz

Reputation: 1305

How to select parents, siblings, children in a category tree

There's a category hierarchy that looks like so:

--Level 1 category 1
  |--Level 2 category 1
     |--Level 3 category 1
     |--Level 3 category 2
     |--Level 3 category 3
  |--Level 2 category 2
     |--Level 3 category 4
     |--Level 3 category 5
     |--Level 3 category 6
--Level 1 category 2
  |--Level 2 category 3
     |--Level 3 category 7

How do I retrieve parents, siblings, and children of any particular category of any level?

For example, for "Level 3 category 5", the parent is "Level 2 category 2", siblings are "Level 3 category 4" and "Level 3 category 6", and no children. For "Level 2 category 3", the parent is "Level 1 category 2", siblings are "Level 2 category 2" and "Level 2 category 1", and a child in "Level 3 category 7";

I'm thinking of using WITH RECURSIVE query, but cannot figure it out how to do it.

Upvotes: 0

Views: 832

Answers (1)

Alexey
Alexey

Reputation: 2479

You only need to use a recursive CTE because you want to get the siblings for the tree nodes, otherwise it's not necessary. A recursive CTE is used to generate level values.

Here is what the query might look like

WITH RECURSIVE cte AS (
    SELECT
        *,
        0 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT
        c.*,
        cte.level + 1
    FROM categories c
    JOIN cte ON c.parent_id = cte.id
)
SELECT 
    c.id,
    c.name,
    c.parent_id,
    cc.name AS parent_name,
    GROUP_CONCAT(DISTINCT s.id) AS sinbling_ids,
    GROUP_CONCAT(DISTINCT s.name) AS sibling_names,
    GROUP_CONCAT(DISTINCT ch.id) AS children_ids,
    GROUP_CONCAT(DISTINCT ch.name) AS childrent_names
FROM cte c
LEFT JOIN categories cc ON c.parent_id = cc.id
LEFT JOIN cte s ON c.level = s.level AND s.id != c.id
LEFT JOIN categories ch ON ch.parent_id = c.id
GROUP BY c.id, c.name, c.parent_id, cc.name

You can check a dbfiddle

Upvotes: 1

Related Questions