order_by() method not working in peewee

Question

I am using a SQLite backend with a simple show - season - episode schema:

class Show(BaseModel):
    name = CharField()

class Season(BaseModel):
    show = ForeignKeyField(Show, related_name='seasons')
    season_number = IntegerField()

class Episode(BaseModel):
    season = ForeignKeyField(Season, related_name='episodes')
    episode_number = IntegerField()

and I would need the following query :

seasons = (Season.select(Season, Episode)
       .join(Episode)
       .where(Season.show == SHOW_ID)
       .order_by(Season.season_number.desc(), Episode.episode_number.desc())
       .aggregate_rows())

SHOW_ID being the id of the show for which I want the list of seasons. But when I iterate over the query with the following code :

for season in seasons:
    for episode in season.episodes:
        print(episode.episode_number)

... I get something which is not ordered at all, and which does not even follow the order I would get without using order_by(), i.e. the insertion order.

I activated the debug logs to see the outgoing query, and the query does contain the ORDER BY clause, and manually applying it returns the proper descending order.

I am new to peewee, and I have seen so many examples making use of a join() combines with an order_by(), but I can still not find out what I am doing wrong.

Answer 1

This was due to a bug in the processing of nested collections in the aggregate query result wrapper.

The github issue is: https://github.com/coleifer/peewee/issues/519

The fix has been merged here: https://github.com/coleifer/peewee/commit/ec0e87f1a480695d98bf1f0d7f2e63aed8dfc440

So, to get the fix you'll need to either clone master or wait til the next release which should be in the next week or two (2.4.7).