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

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    parent_id = Column(Integer, ForeignKey('categories.id'))
    children = relationship('Category', backref='parent', remote_side=[id])

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func, desc

engine = create_engine('数据库连接字符串')
Session = sessionmaker(bind=engine)
session = Session()

categories = session.query(Category).\
    order_by(desc(func.count(Category.children))).\
    group_by(Category.id).\
    all()

for category in categories:
    print(f"分类名称：{category.name}")
    print(f"子分类数量：{len(category.children)}")
    if category.parent:
        print(f"父分类名称：{category.parent.name}")
    else:
        print("没有父分类")
    print("------")