En esta guía se exploran técnicas avanzadas de consulta usando SQLAlchemy dentro de aplicaciones Flask. Se abordan ordenación, límites, paginación, lazy loading, agrupación, joins, alias y subconsultas.

1. Ordenación de resultados

Para ordenar los registros se emplea el método order_by(). Prefijar el campo con un guión (-) indica orden descendente.

# Ejemplo: orden descendente por fecha de creación
posts = Post.query.order_by(Post.created_at.desc()).all()

Es posible definir un orden por defecto en el modelo usando __mapper_args__:

class Article(db.Model):
    __mapper_args__ = {
        "order_by": title
    }
    # ... resto del modelo

Para orden inverso se puede llamar al método .desc() sobre la columna.

2. Limitación, desplazamiento y rebanado

• limit(): restringe el número de filas devueltas.
• offset(): omite las primeras N filas.
• Rebanado (slice): permite usar índices directamente sobre el objeto Query, p.ej. query[5:10].

# Obtener 10 autores empezando desde el 5º
autores = Autor.query.offset(5).limit(10).all()

3. Carga perezosa (Lazy Loading)

Al definir relaciones con lazy='dynamic', el aceso al atributo de relación devuelve un objeto AppenderQuery en lugar de una lista, permitiendo aplicar filtros adicionales.

class Autor(db.Model):
    # ...
    articulos = db.relationship('Articulo', lazy='dynamic')

# Uso: solo artículos de hoy
autor = Autor.query.get(1)
articulos_hoy = autor.articulos.filter(Articulo.fecha == date.today()).all()

4. Agrupación y filtrado avanzado

GROUP BY

Agrupa resultados según una columna, útil con funciones de agregación.

from sqlalchemy import func

resultado = db.session.query(
    Usuario.genero,
    func.count(Usuario.id)
).group_by(Usuario.genero).all()

HAVING

Filtra grupos después de la agregación.

# Usuarios mayores de edad agrupados por edad
resultado = db.session.query(
    Usuario.edad,
    func.count(Usuario.id)
).group_by(Usuario.edad).having(Usuario.edad >= 18).all()

5. Joins (combinaciones)

SQLAlchemy soporta join (inner join por defecto) y outerjoin (left outer join).

# Inner join entre Usuario y Dirección
for usuario, direccion in db.session.query(Usuario, Direccion).join(Direccion).all():
    print(usuario, direccion)

# Left outer join (todos los usuarios aunque no tengan dirección)
for usuario, direccion in db.session.query(Usuario, Direccion).outerjoin(Direccion).all():
    print(usuario, direccion)

6. Alias en consultas

Cuando la misma tabla aparece varias veces, se usan alias para evitar conflictos.

from sqlalchemy.orm import aliased

Dir1 = aliased(Direccion)
Dir2 = aliased(Direccion)
resultados = db.session.query(
    Usuario.nombre,
    Dir1.email,
    Dir2.email
).join(Dir1).join(Dir2).all()

7. Subconsultas

Se constryuen con subquery() y se accede a sus columnas mediante el atributo .c.

from sqlalchemy.sql import func

# Subconsulta: cantidad de direcciones por usuario
subq = db.session.query(
    Direccion.usuario_id.label('uid'),
    func.count('*').label('total')
).group_by(Direccion.usuario_id).subquery()

# Consulta principal con outer join
for usuario, total in db.session.query(
    Usuario,
    subq.c.total
).outerjoin(subq, Usuario.id == subq.c.uid).order_by(Usuario.id):
    print(usuario, total)

Para obtener entidades completas dentro de una subconsulta se usa aliased junto con ella.

subq = db.session.query(Direccion)
DirAlias = aliased(Direccion, subq)
for usuario, direccion in db.session.query(Usuario, subq).join(subq, Usuario.direcciones):
    print(usuario, direccion)