Flask-SQLAlchemy - First unwatched episode from series sorted by last watched

Artificial

Without Intelligence
Reaction score
326
This is what I could come up with:

[gist=query.py]ca666efa4d59a2088209[/gist]

I'm not perfectly certain about whether those outer joins should be outer or inner or somethingelse joins, I'm starting to get a bit confused. But this seems to work. If you want to play it safe, you could just select it as well as you can in SQLAlchemy and then do the fine-tuning in Python.
 

Furby

Current occupation: News poster
Reaction score
144
I don't understand why you put;

Code:
filter(
        episodes.c.air_time >= userseries.c.last_watched

there, doesn't make sense. And the series in your example are not sorted by last_watched from UserSerie.

===== EDIT =====

I got it working with following code:

Code:
episodes = Episode.query.subquery()
userseries = UserSerie.query.subquery()
 
# Get the min air time for each of the favorite series.
min_air_times = db.session.query(
        Serie.id.label('serie_id'),
        db.func.min(episodes.c.id).label('id')
    ).filter(
        Serie.id.in_(x.id for x in u1.favorite_series)
    ).outerjoin(
        userseries,
        Serie.id == userseries.c.serie_id
    ).outerjoin(
        episodes,
        Serie.id == episodes.c.serie_id
    ).filter(
        ~episodes.c.id.in_(x.id for x in u1.watched_episodes)
    ).order_by(
        desc(userseries.c.last_watched)
    ).group_by(
        Serie.id
    ).subquery()
 
# Select the serie and episode.
shows = db.session.query(
        Serie,
        Episode
    ).join(
        Episode,
        Episode.serie_id == Serie.id
    ).join(
        min_air_times,
        db.and_(
            min_air_times.c.serie_id == Serie.id,
            min_air_times.c.id == Episode.id
        )
    ).all()

Thanks for help arti! Post the result on stackoverflow so I can pick your answer :) and give you rep. Since you are such a RW :D
 

Artificial

Without Intelligence
Reaction score
326
I don't understand why you put ... there, doesn't make sense
I understood the last_watched attribute meant that everything before that has been seen already and shouldn't be selected. Didn't notice User had a watched_episodes attribute.

And the series in your example are not sorted by last_watched from UserSerie.
Didn't notice that was a requirement, was too focused on the part that challenged me.

Post the result on stackoverflow so I can pick your answer
Nah, you can just click on the like button here. :p
 
General chit-chat
Help Users

      The Helper Discord

      Staff online

      Members online

      Affiliates

      Hive Workshop NUON Dome World Editor Tutorials

      Network Sponsors

      Apex Steel Pipe - Buys and sells Steel Pipe.
      Top