node.js - SQL child 'categories' 和 child child 'categories' : recursive? 中的所有行

Question

我在编写解决以下问题的查询时遇到问题，我认为这需要某种递归:

我有一个包含houses 的表，每个表都有一个特定的house_type，p.e. house, bungalow, etc. house_types 相互继承，也在一个名为 house_types 的表中声明。

table: houses
id | house_type
1  | house
2  | bungalow
3  | villa
etcetera...

table: house_types
house_type | parent
house      | null
villa      | house
bungalow   | villa
etcetera...

按照这个逻辑，平房也是别墅，别墅也是房子。因此，当我想要获得所有别墅时，应显示房屋 2 和 3，当我想要获得所有房屋时，应显示房屋 1、2 和 3，当我想要所有平房时，应仅显示房屋 3。

递归查询是答案吗？我应该如何解决这个问题。我在 node.js 应用程序中使用 knex/objection.js。

Answer 1

这是一个递归 CTE，它获取层次结构中的每一对:

with recursive house_types as (
      select 'house' as housetype, null as parent union all
      select 'villa', 'house' union all
      select 'bungalow', 'villa'
     ),
     cte(housetype, alternate) as (
       select housetype, housetype as alternate
       from house_types
       union all
       select ht.housetype, cte.alternate
       from cte join
            house_types ht
            on cte.housetype = ht.parent
      )
select *
from cte;

(house_types CTE 只是为了设置数据。)

然后您可以将其加入其他数据以获得层次结构的任何级别。