CODEWITHSUNDEEP
pythonsqlalchemy
Halcyon Abraham Ramirez
Halcyon Abraham Ramirez

Reputation: 1560

SqlAlchemy Group Concat and OrderBy

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?

Upvotes: 0

Views: 2776

Answers (2)

seaders
seaders

Reputation: 4096

.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.

Upvotes: 0

ACV
ACV

Reputation: 1975

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()

Upvotes: 1

Related Questions