如何获取查询中每个基础行的单独结果

Question

我有一个这样的模型：

Category:
    id (primary key)
    parent_id (foreign key)
    name (string)

并且有一个子类别 id 的列表，我正在使用 sqlalchemy 公用表表达式递归查询它们的父项，如下所示：

r = session.query(Category.id, Category.parent_id, *fields).
    filter(Category.id.in_(id_list)).
        cte(name='r', recursive=True)
r_alias = aliased(r, name="recursive")
base_alias = aliased(Category, name='base')
included_parts = r.union_all(
  session.query(base_alias).filter(base_alias.id == r_alias.c.parent_id)
)
q = session.query(included_parts).all()
print(q)

这将产生以下结果(ID 为 [1， 2] 的类别的所有父项：

[(1, None, 'Category1'), (2, 1, 'Category2'), (1, None, 'Category1')]

但是我想单独获取每个类别的列表，我需要的是这样的东西(id_list中每个 id 的单独结果(：

[
    [(1, None, 'Category1')],
    [(2, 1, 'Category2'), (1, None, 'Category1')]
]

有没有办法做到这一点

或使组更容易分离(例如，有没有办法在每个结果中包含来自id_list的相应 id，以便我可以通过它对结果进行分组(通过 SQLAlchemy 手段？

例如，如何获得以下结果而不是我得到的结果：

[(1, 1, None, 'Category1'), (2, 2, 1, 'Category2'), (2, 1, None, 'Category1')]

然后我将能够按每个元组的第一个成员对它们进行分组，以获得所需的组。

Answer 1

简单的解决方案是将原始 id 添加为原始 SELECT 的第一列，并在递归 CTE 的迭代步骤中继续选择这些 id：

In [18]: r = session.query(Category.id.label('orig_id'),
    ...:                   Category.id,
    ...:                   Category.parent_id,
    ...:                   *fields).
    ...:     filter(Category.id.in_(id_list)).
    ...:     cte(name='r', recursive=True)
    ...: 
    ...: r_alias = aliased(r, name="recursive")
    ...: base_alias = aliased(Category, name='base')
    ...: 
    ...: included_parts = r.union_all(
    ...:   session.query(r_alias.c.orig_id,  # Select the originating ID here
    ...:                 base_alias).
    ...:       filter(base_alias.id == r_alias.c.parent_id))
    ...:       
    ...: res = session.query(included_parts).all()
    ...: print(res)
    ...: 
[(1, 1, None, 'first'), (2, 2, 1, 'second'), (2, 1, None, 'first')]