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

Artificial

Without Intelligence
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
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
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
  • No one is chatting at the moment.
  • Ghan Ghan:
    We did!
  • Ghan Ghan:
    The old shoutbox wasn't supported anymore. We updated xenForo, so it had to be replaced.
  • jonas jonas:
    let's see if everyone finds it... the nice thing about the shoutbox was I could check on it even while logged out, but the existence of this one is hidden when you're not logged in
  • Ghan Ghan:
    We can fix that.
  • Ghan Ghan:
    Chat should show on the sidebar when not logged in now.
  • Ghan Ghan:
    (You'll still need to log in to post messages)
  • Ghan Ghan:
    Test!
  • tom_mai78101 tom_mai78101:
    I must be in a test server.
  • tom_mai78101 tom_mai78101:
    Nice, Twitter tweets embedding now works
  • Wizard Wizard:
    Yup.
  • Ghan Ghan:
    Excellent.
  • Ghan Ghan:
    @tom_mai78101 Hello there.
  • Ghan Ghan:
    Tagging works in the chat too.
  • tom_mai78101 tom_mai78101:
    @Ghan Missed it.
  • Wizard Wizard:
    Still fixing things here and there. Added widgets to the portal, will make it match the ones here on the forum index tomorrow.
  • Ghan Ghan:
    The venerable World Editor Tutorials site has been converted to HTTPS at last.
  • jonas jonas:
    cool
  • jonas jonas:
    and I can even edit my messages, nice
  • seph ir oth seph ir oth:
    GENERAL CHIT CHAT, YOU ARE A BOLD ONE
  • Ghan Ghan:
    Hello there
  • The Helper The Helper:
    this new chatbox is great and the forum software update is great too
    +1
  • The Helper The Helper:
    upgrade has fixed forum registration spam problem
  • tom_mai78101 tom_mai78101:
    Something tells me we might be able to customize the chatbox a bit, considering that there's a gap under every message.
  • Wizard Wizard:
    Going to deploy a fix soon, just had to take some time for myself this weekend.

    Members online

    No members online now.

    Affiliates

    Hive Workshop
    Top