8

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

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
minhee
  • 5,688
  • 5
  • 43
  • 81
  • @IljaEverilä Completely different topic. That's about aggregate functions, and this is about aggregate expressions. – minhee Nov 16 '17 at 08:16
  • I'd not go so far as to say completely different, but here's a better dupe candidate: https://stackoverflow.com/questions/46167101/sqlalchemy-count-status-is-true, also mentioned [here](https://stackoverflow.com/questions/37328779/sqlalchemy-func-count-on-boolean-column/37333820). The relevant documentation: [`funcfilter()`](http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.funcfilter) and [`FunctionElement.filter()`](http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.FunctionElement.filter). – Ilja Everilä Nov 16 '17 at 08:18

1 Answers1

14

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)
bakkal
  • 54,350
  • 12
  • 131
  • 107