python之在 SQLAlchemy 中按自引用子集合的长度对对象进行排序

我有一个表示层次结构的数据库表，这意味着它有一个自引用外键。我想根据对象的 child 数量对对象进行排序。

问题是我既不知道如何进行适当的自连接，也不知道如何在原始查询中请求子集合的计数。结果是我不得不求助于检索子项、获取子集合长度并在 Python 中对结果进行排序。

from sqlalchemy import * 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy.orm import sessionmaker, relationship 
Base = declarative_base() 
 
engine = create_engine("...") 
Session = sessionmaker(bind=engine) 
session = Session() 
 
class Variable(Base): 
    __tablename__ = 'variable' 
 
    id          = Column(Integer, primary_key=True) 
    parent_id   = Column(Integer, ForeignKey('variable.id')) 
    parent = relationship('Variable', remote_side=[id], backref="children") 
 
# Works fine 
for v in session.query(Variable).all(): 
    print(len(v.children)) 
 
# Works fine 
for v in session.query(Variable.id).all(): 
    print(v) 
 
# AttributeError: type object 'Variable' has no attribute 'children' 
for v in session.query(func.count(Variable.children)).all(): 
    print(v) 
 
# AttributeError: type object 'Variable' has no attribute 'children' 
for v in session.query(Variable.children).all(): 
    print(v) 

它似乎认为它不知道children，但只在某些情况下。作为实验，我尝试显式添加 children:

children = relationship('Variable', backref="parent") 

我收到以下错误:

Error creating backref 'parent' on relationship 'Variable.children': property of that name exists on mapper 'Mapper|Variable|variable' 

以下解决了这个问题，但这是一个暴行:我正在拉动整个集合只是为了计算它，并且我正在对客户端进行排序。如何让 SQLAlchemy 在所有数据库端执行此操作？

import operator 
vars = {} 
for v in db.session.query(Variable).all(): 
    vars[v.id] = len(v.children) 
 
sorted_vars = sorted(vars.items(), key=operator.itemgetter(1)) 

请您参考如下方法:

获取直接子节点数的一种方法是按 parent_id 分组并计数，但正如您已经注意到的那样，您会丢失没有子节点的叶节点。要解决此问题，您可以创建计数的子查询并返回 Variable，将 NULL 值合并为 0。另一方面，在这种情况下不需要子查询:

child = aliased(Variable) 
session.query(Variable, 
              func.count(child.id).label('child_count')).\ 
    outerjoin(child, Variable.children).\ 
    group_by(Variable.id).\ 
    order_by(literal_column('child_count')).\ 
    all() 

由于保证主键不为 NULL，因此计数将只为没有匹配的右侧或没有子项的左侧产生 NULL 值。如果您对实际计数不感兴趣，请将其完全移动到 ORDER BY 子句中。