python - Flask-SQLAlchemy - 按上次观看排序的系列中第一集未观看的剧集

Question

在这里，我再次需要您的帮助。

我想从用户最喜欢的系列中检索每个系列的一集，这是第一个未观看的(所以下一个要观看)和系列按最后观看排序。

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
import logging
from datetime import datetime
from sqlalchemy.ext.associationproxy import association_proxy

app = Flask(__name__)
db = SQLAlchemy(app)

logging.basicConfig()

watched_episodes = db.Table('watched_episodes',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('episode_id', db.Integer, db.ForeignKey('episode.id'))
)


class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    favorite_series = association_proxy('user_series', 'serie')
    watched_episodes = db.relationship('Episode', secondary=watched_episodes,
        backref=db.backref('users', lazy='dynamic'))

    def __repr__(self):
        return '<User {0}>'.format(self.name)

    def __init__(self, name):
        self.name = name


class UserSerie(db.Model):
    __tablename__ = 'user_series'
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), \
        primary_key=True)
    serie_id = db.Column(db.Integer, db.ForeignKey('serie.id'), \
        primary_key=True)
    last_watched = db.Column(db.DateTime)
    user = db.relationship("User", \
        backref=db.backref("user_series", cascade="all, delete-orphan"))
    serie = db.relationship("Serie")

    def __init__(self, serie=None, user=None, last_watched=None):
        self.user = user
        self.serie = serie
        self.last_watched = datetime.now()


class Serie(db.Model):
    __tablename__ = 'serie'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '<Serie {0}>'.format(self.name)


class Episode(db.Model):
    __tablename__ = 'episode'
    id = db.Column(db.Integer, primary_key=True)
    air_time = db.Column(db.DateTime)
    seas_num = db.Column(db.Integer)
    epis_num = db.Column(db.Integer)
    serie_id = db.Column(db.Integer, db.ForeignKey('serie.id'))
    serie = db.relationship('Serie',
        backref=db.backref('episodes', lazy='dynamic'))

    def __init__(self, air_time, seas_num, epis_num):
        self.air_time = air_time
        self.seas_num = seas_num
        self.epis_num = epis_num

    def __repr__(self):
        return '<Episode S{0}E{0}>'.format(self.seas_num, self.epis_num)

u1 = User('user1')

s1 = Serie('Serie1')
s2 = Serie('Serie2')

e1 = Episode(datetime(2008, 05, 30), 1, 1)
e1.serie = s1
e2 = Episode(datetime(2008, 06, 05), 1, 1)
e2.serie = s1
e3 = Episode(datetime(2008, 06, 10), 1, 1)
e3.serie = s1

e4 = Episode(datetime(2012, 01, 01), 1, 1)
e4.serie = s2
e5 = Episode(datetime(2012, 02, 12), 1, 1)
e5.serie = s2
e6 = Episode(datetime(2012, 03, 23), 1, 1)
e6.serie = s2

u1.favorite_series.extend([s1, s2])

db.session.add(u1)
db.session.add(s1)
db.session.add(s2)
db.session.add(e1)
db.session.add(e2)
db.session.add(e3)
db.session.add(e4)
db.session.add(e5)
db.session.add(e6)
db.create_all()
db.session.commit()

logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

sub = db.session.query(UserSerie.serie_id, UserSerie.last_watched).\
    group_by(UserSerie.serie_id).subquery()

sub2 = db.session.query(Episode).\
    filter(Episode.serie_id.in_(x.id for x in u1.favorite_series)).\
    order_by(Episode.air_time).\
    group_by(Episode.serie_id).subquery()

shows = db.session.query(Serie, sub2).\
    outerjoin(sub, Serie.id == sub.c.serie_id).\
    outerjoin(sub2).\
    filter(Serie.id.in_(\
        x.id for x in u1.favorite_series)).\
    order_by(sub.c.last_watched)

for s in shows:
    print s

虽然这会返回:

(<Serie Serie1>, 3, datetime.datetime(2008, 6, 10, 0, 0), 1, 1, 1)
(<Serie Serie2>, 6, datetime.datetime(2012, 3, 23, 0, 0), 1, 1, 2)

但我希望有 Episode 作为对象:

(<Serie Serie1>, <Episode S01E01>)
(<Serie Serie2>, <Episode S01E01>)

在此先感谢您的帮助。

Answer 1

好的。似乎没有人能够帮助我。我又一次得到了 friend Artificial 的帮助。

问题已通过以下代码解决:

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)
    ).filter(
        episodes.c.seas_num != 0
    ).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()