Can I use aggregate expressions using SQLAlchemy?

Question

PostgreSQL have aggregate expressions, e.g. count(*) FILTER (WHERE state = 'success'). How can I generate such expressions using SQLAlchemy?

Answer 1

Suppose I have a model Machine with a boolean field active, and would like to filter the count by active = true

Using func.count(...).filter(...)

from models import db, Machine
from sqlalchemy.sql import func

query = db.session.query(
    func.count(Machine.id).filter(Machine.active == True)
    .label('active_machines')
)

We can look at the generated SQL query:

>>> print(query)
SELECT count(machine.id) FILTER (WHERE machine.active = true) AS active_machines 
FROM machine

This should work the same for the other aggregate functions like func.avg, func.sum, etc

Longer syntax using funcfilter(count(...), filter)

func.count(Machine.id).filter(Machine.active == True) is short hand for:

from sqlalchemy import funcfilter

funcfilter(func.count(Machine.id), Machine.active == True)