mysql - Union two tables with categories in a query that retrieves categories and its parents

Question

I have a PHP function that returns me an array with category information. It is supposed to be provided with a $categoryId, and then return that category and any category "above" it. This is working:

Table structure of tbl_categories:

CREATE TABLE `tbl_categories` (
  `categoryId` int(11) NOT NULL,
  `categoryParentId` int(11) NOT NULL,
  `categoryName` varchar(50) NOT NULL,
  `categoryDescr` varchar(400) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `tbl_categories` ADD PRIMARY KEY (`categoryId`);
ALTER TABLE `tbl_categories`
  MODIFY `categoryId` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

Code of MySQL query:

SELECT * FROM (
    SELECT
      @r AS _id,
      (SELECT @r := categoryParentId FROM tbl_categories WHERE categoryId = _id) AS categoryParentId,
      @l := @l + 1 AS categoryDepth
    FROM
      (SELECT @r := '.$categoryId.', @l := 0) vars,
      tbl_categories h
WHERE @r <> 0) T1
JOIN tbl_categories T2 ON T1._id = T2.categoryId
ORDER BY T1.categoryDepth DESC

But now, instead of one table I have two:

1. tbl_categories
2. tbl_categories_custom

They are identical in structure. The categoryId in tbl_categories_custom starts from 1000 and will not "collide" with the ones in tbl_categories Categories in tbl_categories_custom can have their categoryParentId set to a categoryId in tbl_categories.

Now, my question is, how can I change my query above that works with just tbl_categories, to also work with tbl_categories_custom. I think I must UNION these tables first somehow, but I don't understand how.

Answer 1

First, your version of MySQL (MariaDB 10.3) supports common table expressions, so you have a way to avoid the use of mutating variables in your queries. Mutating variables in a query has been a way to perform hierarchical queries before common table expressions were supported, but it is a tactic that is deprecated and for which there is no documented guarantee that it will always work as intended.

So here is the query to do the same with a common table expression (cte), where 8 is the example value (replace it with the PHP expression):

with recursive 
cte as (
    select 1 as categoryDepth,
           c.* 
    from   tbl_categories c
    where  categoryId = 8
    union
    select cte.categoryDepth + 1, 
           c.*
    from   cte
    inner join tbl_categories c
            on c.categoryId = cte.categoryParentId
)
select   * 
from     cte
order by categoryDepth desc;

And now, when you have this second table, you can first do a common table expression for defining the union, and then continue as above:

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 = 8
    union
    select cte.categoryDepth + 1, 
           base.*
    from   cte
    inner join base
            on base.categoryId = cte.categoryParentId
)
select   *
from     cte
order by categoryDepth desc;