issue with group_concat and op

[Thomas Drake]

Hi,

I'm running into an issue in 0.5.2 and 0.6.1beta where it appears that
group_concat and .op are producing incorrect SQL.

q = session.query(func.group_concat(unionq.c.status.op('order
by')(q.c.stop))

where unionq is essentially a union_all().subquery() of several
queries.

(ProgrammingError) (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'order by anon_2.stop)) AS group_concat_3 \nFROM
jobs, known_components AS known_c' at line 2")

Resulting sql code appears to have an extra set of parentheses? :

group_concat((anon_2.status order by anon_2.stop))

Any ideas? Using the 'seperator' op instead of the 'order by' produces
a similar error, and the group_concat works without the .op...

Thanks,

-thomas

[Thomas Drake]

Wow, my above example sucked. Here's something close to what I'm
actually using:

qs = ...
qunion = qs[0].union_all(*qs[1:]).subquery()

joined = session.query( KnownComponents.name,
qunion,
func.group_concat(qunion.c.status.op('order
by')(qunion.c.stop))
). \
join(qunion).filter
(KnownComponents.id==qunion.c.known_components_id)
joined = joined.group_by( KnownComponents.name,
KnownComponents.version,
qunion.c.serial,
qunion.c.dependent_version,
qunion.c.compile_type )
rows = joined.all()

[Michael Bayer]

op() is old school. make a custom expression element:


from sqlalchemy.ext import compiler
from sqlalchemy.sql import ColumnElement

class group_concat(ColumnElement):
def __init__(self, col1, col2):
self.col1 = col1
self.col2 = col2
self.type = col1.type

@compiler.compiles(group_concat, 'mysql')
def compile_group_concat(element, compiler, **kw):
return "GROUP CONCAT(%s ORDER BY %s)" % (
compiler.process(element.col1),
compiler.process(element.col2)
)


from sqlalchemy import *

m = MetaData()
t = Table('t1', m, Column('foo', String), Column('bar', String))

print select([group_concat(t.c.foo,
t.c.bar)]).compile(bind=create_engine('mysql://'))

"SELECT GROUP CONCAT(t1.foo ORDER BY t1.bar) AS anon_1"


> >
>

[Thomas Drake]

Kickass! This is fantastic. Thanks!