在检索类别及其父项的查询中将两个具有类别的表联合起来 [英] Union two tables with categories in a query that retrieves categories and its parents

问题描述

我有一个 PHP 函数，它返回一个包含类别信息的数组.它应该被提供一个 $categoryId，然后返回该类别和任何类别above".它.这是有效的:

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:

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;

MySQL 查询代码:

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

它们在结构上是相同的.tbl_categories_custom 中的 categoryId 从 1000 开始，不会碰撞"与 tbl_categories 中的那些tbl_categories_custom 中的类别可以将它们的 categoryParentId 设置为 tbl_categories 中的 categoryId.

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.

现在，我的问题是，如何将我上面仅适用于 tbl_categories 的查询更改为也适用于 tbl_categories_custom.我想我必须先以某种方式联合这些表，但我不明白如何.

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.

推荐答案

首先，您的 MySQL (MariaDB 10.3) 版本支持公共表表达式，因此您可以避免在查询中使用变异变量.在支持公共表表达式之前，在查询中改变变量是一种执行分层查询的方法，但这是一种策略已弃用，并且没有文件保证它始终按预期工作.

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.

所以这里是使用公共表表达式 (cte) 执行相同操作的查询，其中 8 是示例值(将其替换为 PHP 表达式):

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;

这篇关于在检索类别及其父项的查询中将两个具有类别的表联合起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！