SQLAlchemy group_concat ordered values

Question

I want simulate the behaviour of collect_set in SQLAlchemy (using MySQL connector). i.e. group A 1,2,3 would be the same as 2,1,3. I have the following code:

res = db.session \
    .query(T1.col1.label('col1'), func.group_concat(T1.col2.distinct()).label('col2_group')) \
    .group_by(T1.col1) \
    .all()

However, by default SQLAlchemy does not order the values in the group, so duplicates may occur.

Is there any way to simulate collect_set behaviour?

Answer 1

Unfortunately SQLAlchemy has official aggregate ORDER BY support for Postgresql only. You can still express it in other dialects using the generic op() operator function, though a bit hackishly:

func.group_concat(T1.col2.distinct().op("ORDER BY")(T1.col2))

which would compile to something like

group_concat(DISTINCT t1.col2 ORDER BY t1.col2)

Answer 2

You can use order_by with group_concat with mysql

func.group_concat(T1.col2.distinct(), order_by=T1.col2.asc())

would be compliled to:

group_concat(DISTINCT col2 ORDER BY col2 ASC)