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)
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)() Base.metadata.create_all(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]) session.commit() 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.append(the_wire) session.commit() lt.favorite_series # > [The Wire] [s.episodes for s in lt.favorite_series] # >> [[The Wire S1E1, The Wire S1E2]]
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
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.