SQLAlchemy: retrieve all episodes from favorite_series of specific user


I have user who can have his favorite series and there are episodes which have series as foreign key and I am trying to retrieve all episodes from favorite series of user. I am using Flask-SQLAlchemy.


db = SQLAlchemy(app)

# cross table for user-series
favorite_series = db.Table('favorite_series',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('series_id', db.Integer, db.ForeignKey('series.id'))

# user
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    favorite_series = db.relationship('Series', secondary=favorite_series,
        backref=db.backref('users', lazy='dynamic'))

# series
class Series(db.Model):
     __tablename__ = 'series'
    id = db.Column(db.Integer, primary_key=True)

# episode
class Episode(db.Model):
    __tablename__ = 'episode'
    id = db.Column(db.Integer, primary_key=True)
    series_id = db.Column(db.Integer, db.ForeignKey('series.id'))
    series = db.relationship('Series',
        backref=db.backref('episodes', lazy='dynamic'))

Friend helped me with SQL

select user_id,series.name,episode.name from (favorite_series left join series on favorite_series.series_id = series.id) left join episode on episode.series_id = series.id where user_id=1;

Altough, I want it in SQLAlchemy API, but can't manage to get it working.


My final working result:

episodes = Episode.query.filter(Episode.series_id.in_(x.id for x in g.user.favorite_series)).filter(Episode.air_time!=None).order_by(Episode.air_time)
5/15/2012 8:29:33 AM

Accepted Answer

First of all you don't seem to be declaring your table names? Also, the whole point of bothering with orm is so you never have to write sql queries:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import orm
import sqlalchemy as db
Base = declarative_base()

favorite_series = db.Table('favorite_series', Base.metadata,
    db.Column('user_id', db.Integer, db.ForeignKey('User.id')),
    db.Column('series_id', db.Integer, db.ForeignKey('Series.id'))
class Episode(Base):
    __tablename__ = 'Episode'
    id = db.Column(db.Integer, primary_key=True)
    season = db.Column(db.Integer)
    episode_num = db.Column(db.Integer)
    series_id = db.Column(db.Integer, db.ForeignKey('Series.id'))

    def __init__(self, season, episode_num, series_id):
        self.season = season
        self.episode_num = episode_num
        self.series_id = series_id

    def __repr__(self):
        return self.series.title + \
               ' S' + str(self.season) + \
               'E' + str(self.episode_num)

class Series(Base):
    __tablename__ = 'Series'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    episodes = orm.relationship('Episode', backref='series')

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

    def __repr__(self):
        return self.title

class User(Base):
    __tablename__ = 'User'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    favorite_series = orm.relationship('Series', 
        secondary=favorite_series, backref='users')

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

    def __repr__(self):
        return self.name

Now you can just access the attributes of your objects and let sql alchemy deal with keeping you DB in sync and issuing queries.

engine = db.create_engine('sqlite:///:memory:')
session = orm.sessionmaker(bind=engine)()

lt = User('Ludovic Tiako')
the_wire = Series('The Wire')
friends = Series('Friends')
session.add_all([lt, the_wire, friends])
session.commit() # need to commit here to generate the id fields

tw_s01e01 = Episode(1,1,the_wire.id)
tw_s01e02 = Episode(1,2,the_wire.id)
f_s01e01 = Episode(1,1,friends.id)
f_s01e02 = Episode(1,2,friends.id)
f_s01e03 = Episode(1,3,friends.id)

session.add_all([tw_s01e01, tw_s01e02, 
                f_s01e01, f_s01e02, f_s01e03])

the_wire.episodes # > [The Wire S1E1, The Wire S1E2]
friends.episodes # > [Friends S1E1, Friends S1E2, Friends S1E3]

Finally, to answer your question:

lt.favorite_series # > [The Wire]
[s.episodes for s in lt.favorite_series] # >> [[The Wire S1E1, The Wire S1E2]]
5/13/2012 2:45:16 PM

I don't know about Flask, but from the docs of Flask-SQLAlchemy, it seems it uses declarative, so the ORM. And so, you should have a session. I think it is accessible to you from db.session.

Anyway, if those assumptions are true, this is how you should do it:

query = db.session.query(User.id, Series.name, Episode.name).filter((Episode.series_id == Series.id) & \
    (User.id == favorite_series.c.user_id) & (Series.id == favorite_series.c.id) & \
    (User.id == 1))
results = query.all();

It might not be the exact query you provided, but should do the same.

UPDATE: I just checked Flask-SQLALchemy code on github, it seems that db is an instance of SQLAlchemy, which has a session attribute, created by self.session = self.create_scoped_session(session_options) which returns a session object. So this should work.

Also, not that by doing that, you won't be using their BaseQuery, although I don't know what that would mean...

Check the documentation to know what to do exactly.

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow