Welcome to 16892 Developer Community-Open, Learning,Share
menu search
person

Categories

Currently I use the following CTE to grab a category and any category below it:

WITH RECURSIVE tree AS (
SELECT * FROM (
    SELECT categoryId, 
          categoryName, 
          categoryParentId,
          categoryDescr,
          categoryType,
          categoryDC,
          categoryLedgerId,
          1 as categoryDepth
    FROM tbl_categories
    WHERE categoryId = '.$categoryId.'
    UNION
    SELECT categoryId, 
          categoryName, 
          categoryParentId,
          categoryDescr,
          categoryType,
          categoryDC,
          categoryLedgerId,
          1 as categoryDepth
    FROM tbl_categories_custom
    WHERE categoryId = '.$categoryId.'
) AS combined

UNION ALL

SELECT p.categoryId,
        p.categoryName,
        p.categoryParentId,
        p.categoryDescr,
        p.categoryType,
        p.categoryDC,
        p.categoryLedgerId, 
        t.categoryDepth + 1
        FROM (
           SELECT * FROM tbl_categories
           UNION
           SELECT * FROM tbl_categories_custom
        ) AS p
        JOIN tree t ON t.categoryId = p.categoryParentId
)
SELECT *
FROM tree

However, as @trincot showed me in Union two tables with categories in a query that retrieves categories and its parents it can be done much cleaner.

His version over there grabs a category and any category above it.

This one should do it the opposite way; grab any category and any category below it. Which it does. But it seems overcomplicated now that I read his other version.

How to simplify this CTE?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
355 views
Welcome To Ask or Share your Answers For Others

1 Answer

Indeed, you can make this shorter by first making a common table expression for the union, and only then doing the recursive one.

The query is quite similar to the one posted in the other question. The major change is that the join condition in the inner join ... on clause is now reversed. The = 1 part is where you would do the comparison with your PHP variable:

with recursive 
base as (
    select * from tbl_categories
    union
    select * from tbl_categories_custom
),
cte as (
    select 1 as categoryDepth,
           base.* 
    from   base
    where  categoryId = 1
    union
    select cte.categoryDepth + 1, 
           base.*
    from   cte
    inner join base
            on cte.categoryId = base.categoryParentId
)
select   *
from     cte;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to 16892 Developer Community-Open, Learning and Share
...