SqlAlchemy Group Concat and OrderBy

Question

movies_on_date = session.query(movies_showing,
                                       movies,
                                       func.group_concat(movies_showing.movie_showtime)\
                                            .label("showtimes"))\
                                            .filter(movies_showing.date == movie_date,
                                                    movies.movie_title == movies_showing.movie_title) \
                                            .group_by(movies_showing.movie_title)\
                                            .all()

how do we do a group concat and order them?

i've tried:

func.group_concat(movies_showing.movie_showtime).asc()

and:

func.group_concat(movies_showing.movie_showtime.asc())

and both doesn't seem to work. how do we achieve that?

try "func.group_concat(movies_showing.movie_showtime.distinct())" — SumanKalyan, Jan 04 '17 at 07:24
@SumanKalyan im not looking for unique values. Im looking to aggregate the values but aggregate them in an ascending or descending order — Halcyon Abraham Ramirez, Jan 04 '17 at 15:44
Was there ever a solution found for this? Can any or all of these features be used in sqlalchemy's ORM? https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat — Dan Garthwaite, Mar 06 '17 at 14:48

score 1 · Answer 1 · edited May 23 '17 at 11:46

I'm assuming that your group_concat is already working and you just want to add ordering to the query. If that's the case I think you cab use the "showtimes" label you created as the ordering argument as shown here:

https://stackoverflow.com/a/15557759/1216837

So I think the query could as slightly modified like this:

from sqlalchemy import desc

... 
.group_by(movies_showing.movie_title)\
.order_by(desc('showtimes')).all()
.all()

score 0 · Answer 2 · answered May 10 '18 at 15:55

.op is your friend, http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.operators.Operators.op

func.group_concat(movies_showing.op('ORDER BY')(movies_showing.movie_showtime.asc())

I think it's a bug in sqlalchemy's code, as they're leaving out that "ORDER BY movie_showtime" in the SQL they produce, but that's how to get around it.

SqlAlchemy Group Concat and OrderBy

2 Answers2

Linked