Me cuesta mucho optimizar mis consultas de SQLAlchemy. Mi conocimiento de SQL es muy básico, y no puedo obtener lo que necesito de los documentos de SQLAlchemy.

Suponga la siguiente relación muy básica de uno a muchos:

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key = True)
    children = relationship("Child", backref = "parent")

class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key = True)
    parent_id = Column(Integer, ForeignKey("parents.id"))
    naughty = Column(Boolean)

Cómo podría:

Consulta tuplas de(Parent, count_of_naughty_children, count_of_all_children) para cada padre?

Después de pasar un buen tiempo buscando en Google, descubrí cómo consultar esos valores por separado:

# The following returns tuples of (Parent, count_of_all_children):
session.query(Parent, func.count(Child.id)).outerjoin(Child, Parent.children).\
    group_by(Parent.id)
# The following returns tuples of (Parent, count_of_naughty_children):
al = aliased(Children, session.query(Children).filter_by(naughty = True).\
    subquery())
session.query(Parent, func.count(al.id)).outerjoin(al, Parent.children).\
    group_by(Parent.id)

Traté de combinarlos de diferentes maneras, pero no pude obtener lo que quería.

¿Consulta a todos los padres que tienen más del 80% de hijos traviesos? Editar: travieso podría ser NULL.

Supongo que esta consulta se basará en la anterior, filtrando por relación travieso / todo.

Cualquier ayuda es apreciada.

EDITAR: Gracias a la ayuda de Antti Haapala, encontré la solución a la segunda pregunta:

avg = func.avg(func.coalesce(Child.naughty, 0)) # coalesce() treats NULLs as 0
# avg = func.avg(Child.naughty) - if you want to ignore NULLs
session.query(Parent).join(Child, Parent.children).group_by(Parent).\
    having(avg > 0.8)

Encuentra promedio si los niñosnaughty variable, tratando False y NULLs como 0, y True como 1. Probado con MySQL backend, pero también debería funcionar en otros.