Как в SQLAlchemy отсортировать результат по количеству имеющихся child в relationship

Question

У меня есть модель User:

class User(BaseModel):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    parent_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    childs: Mapped[list["User"]] = relationship()

Я хочу вывести пользователей с самым наибольшим количеством childs, но не представляю как.

Я пробовал:

func.count(User.childs)
order_by(desc(User.childs))

... но не сработало. Получилось достичь приблизительно результата только так

        subquery = (
            select(User.parent_id, func.count().label("count"))
            .group_by(User.parent_id)
            .order_by(desc("count"))
            .limit(10)
            .subquery()
        )
        result = await session.execute(
            select(User, subquery.c.count)
            .where(User.id == subquery.c.parent_id)
            .order_by(subquery.c.count.desc())
            .limit(10)
        )

Но мне кажется, есть решение поизящнее

Answer 1

В целом нашёл такой вариант

child = aliased(User)
stmt = (
            select(User, func.count(child.id).label("count"))
            .outerjoin(child, User.childs)
            .order_by(literal_column("count").desc())
            .group_by(User.id)
            .limit(10)
        )

БЛОГ НА HUSL

Как в SQLAlchemy отсортировать результат по количеству имеющихся child в relationship

Ответы (1 шт):