如何仅将 LIMIT 应用于父行

Luke 发表于 Dev

卢克

在我的 Postgres 数据库中，我有一个包含简单层次结构的表，如下所示：

id | parent_id
---------------

当表中的项目是“顶级”项目时，它parent_id被设置为NULL

但是，当我查询我的表时，我会检索顶级项目和属于这些项目的子项目。例如，如果有一个带有两个孩子的顶级项目，我的查询将返回三行。我的查询非常简单，它看起来像这样：

SELECT
  *
FROM
  my_table
LIMIT
  _limit
OFFSET
  _offset
;

当上述返回三行时，在我的业务逻辑中，我将该结果转换为 JSON 结构，然后序列化到客户端。它看起来像这样：

items: [
  {
    id: 1,
    parent_id: null,
    items: [
      {
        id: 2,
        parent_id: 1
      },
      {
        id: 3,
        parent_id: 1
      }
    ]
  }
]

然而，正如你可以看到我的查询有OFFSET和LIMIT对，你猜对了，分页。该表非常大，我想限制单个请求中可以请求的项目数量。

问题是，继续使用我的单个顶级项目作为示例，如果LIMIT设置为1，则将永远不会返回顶级项目的子项。

我基本上要寻找的是一种方法来排除子行计数到LIMIT，或者，扩展LIMIT与找到的子行总数。

米兹

你将不得不做两件事：

获取要包含的顶级条目（分页）
对顶层的后代运行另一个查询

这是一个完全递归的例子

create table t (id int primary key, parent_id int);

insert into t (id, parent_id) values
  (1, null), (2, null), (3, null), (4, 1),
  (5, 1), (6, 4), (7, 2), (8, 2),
  (9, 8), (10, 3), (11, null), (12, null);


with recursive entries (id, parent_id) as (
  (
   select 
     id, parent_id 
   from t 
   where parent_id is null
   order by id limit 2 -- add offset N here
  ) 
  union all
  (
    select 
      t.id, t.parent_id
    from entries inner join t on (t.parent_id = entries.id) 
  )
) 
select * from entries;

https://www.db-fiddle.com/f/g3G2t3mVo7fBhQa9QCA71P/0

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。