string_agg() with order by clause

[David Gardner]

Recently Postgres added a new aggregate function called string_agg().
I have been able to use it like:
Session.query(Asset, func.string_agg(some_col, ','))

This works, but according to the docs I should be able to do
string_agg(some_col, ',' ORDER BY some_col)
Is there a way to do this in SQLAlchemy?

--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgar...@creatureshop.com

[David Gardner]

I should have linked to the docs in question
http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES

[Conor]

On 08/27/2010 05:06 PM, David Gardner wrote:

I should have linked to the docs in question
http://developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-AGGREGATES

On 08/27/2010 03:03 PM, David Gardner wrote:

Recently Postgres added a new aggregate function called string_agg().
I have been able to use it like:
Session.query(Asset, func.string_agg(some_col, ','))

This works, but according to the docs I should be able to do
string_agg(some_col, ',' ORDER BY some_col)
Is there a way to do this in SQLAlchemy?

I think you have to write your own compiler extension:

import sqlalchemy as sa
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import ColumnElement
from StringIO import StringIO

class string_agg(ColumnElement):
    type = sa.String()

    def __init__(self, expr, separator=None, order_by=None):
        self.expr = expr
        self.order_by = order_by
        self.separator = separator

@compiles(string_agg, 'mysql')
def _compile_string_agg_mysql(element, compiler, **kw):
    buf = StringIO()
    buf.write('group_concat(')
    buf.write(compiler.process(element.expr))
    if element.order_by is not None:
        buf.write(' ORDER BY ')
        buf.write(compiler.process(element.order_by))
    if element.separator is not None:
        buf.write(' SEPARATOR ')
        buf.write(compiler.process(sa.literal(element.separator)))
    buf.write(')')
    return buf.getvalue()

# Use 'postgres' for SQLAlchemy < 0.6.
@compiles(string_agg, 'postgresql')
def _compile_string_agg_postgresql(element, compiler, **kw):
    buf = StringIO()
    buf.write('string_agg(')
    buf.write(compiler.process(element.expr))
    if element.separator is not None:
        buf.write(', ')
        buf.write(compiler.process(sa.literal(element.separator)))
    if element.order_by is not None:
        buf.write(' ORDER BY ')
        buf.write(compiler.process(element.order_by))
    buf.write(')')
    return buf.getvalue()

if __name__ == '__main__':
    clause = string_agg(sa.literal_column('some_column'),
                        ', ',
                        order_by=sa.literal_column('some_other_column').asc())

    mysql_engine = sa.create_engine('mysql:///')
    print 'MySQL: %s' % clause.compile(dialect=mysql_engine.dialect)

    pg_engine = sa.create_engine('postgresql:///')
    print 'PostgreSQL: %s' % clause.compile(dialect=pg_engine.dialect)

-Conor

[David Gardner]

Fantastic! That works.

Out of curiosity I noticed that the compile function expects to receive instances of Column.
This isn't a big problem because I just reverted to doing table_var.c.my_col, but is there
a simpler way to use MyClassName.Col?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Aug 27, 2010, at 8:56 PM, David Gardner wrote:

Fantastic! That works.

Out of curiosity I noticed that the compile function expects to receive instances of Column.
This isn't a big problem because I just reverted to doing table_var.c.my_col, but is there
a simpler way to use MyClassName.Col?

you coerce incoming arguments into expressions at the constructor level:

from sqlalchemy.sql.expression import _literal_as_column

class MyWhatever(ColumnElement):

    def __init__(self, expr, ...):

        self.expr = _literal_as_column(expr)

_literal_as_column basically calls __clause_element__() on the incoming object, and if not present tries turning it into a literal_column() if its a string.  You might also try _no_literals, _only_column_elements, etc. based on what you'd expect to see there, all of which do the __clause_element__() conversion.

[David Gardner]

On 08/27/2010 09:21 PM, Michael Bayer wrote:
> you coerce incoming arguments into expressions at the constructor level:
>
> from sqlalchemy.sql.expression import _literal_as_column
>
> class MyWhatever(ColumnElement):
> def __init__(self, expr, ...):
> self.expr = _literal_as_column(expr)
>
>
> _literal_as_column basically calls __clause_element__() on the
> incoming object, and if not present tries turning it into a
> literal_column() if its a string. You might also try _no_literals,
> _only_column_elements, etc. based on what you'd expect to see there,
> all of which do the __clause_element__() conversion.
>
>

Thank you _literal_as_column() worked for me. This allows me to use a
column from an aliased class.