Alex jg Alex jg - 6 months ago 74
Python Question

Using PostgreSQL Aggregate ORDER BY with sqlalchemy

I have a query which uses PostgreSQL's syntax for using ORDER BY in an aggregate function, something like this:

SELECT some_agg_func(a ORDER BY b DESC) FROM table;

Does anyone know of anyway to do this with the sqlalchemy expression language?


You need to use SQLAlchemy's compiler extension to achieve this. Here's an example for Postgres string_agg function:

from sqlalchemy.sql.expression import ColumnElement, _literal_as_column
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.compiler import compiles

class string_agg(ColumnElement):
    def __init__(self, expr, separator, order_by=None):
        self.type = Text()
        self.expr = _literal_as_column(expr)
        self.separator = literal(separator)
        self.order_by = _literal_as_column(order_by)

    def _from_objects(self):
        return self.expr._from_objects

@compiles(string_agg, 'postgresql')
def compile_string_agg(element, compiler, **kwargs):
    head = 'string_agg(%s, %s' % (
    if element.order_by is not None:
        tail = ' ORDER BY %s)' % compiler.process(element.order_by)
        tail = ')'
    return head + tail

query = session.query(string_agg(, ', ',
# Print compiled SQL query.
print query.statement.compile(dialect=postgresql.dialect())
# Run the query and print result.
print query.scalar()