Accessing collection of collection efficiently in sqlalchemy

Question

I am fairly new to sqlalchemy and SQL in general. I have the following models

class User(Base):
    """Stores users and their magazines"""
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    magazines = relationship('Magazine', secondary=magazine_association)

class Magazine(Base):
    """Stores magazines and their articles"""
    __tablename__ = 'magazine'
    id = Column(Integer,primary_key = True)
    articles = relationship('Article',cascade="save-update, merge, delete",backref='magazine',lazy='dynamic')

class Article(Base):
    """Stores article information"""
    __tablename__ = 'article'
    id = Column(Integer, primary_key = True)
    title = Column(String(240), default='Unknown Title')
    category = Column(String(40), default='Unknown category')
    rank = Column(Integer, default=0)
    #Backrefs
    magazine_id = Column(Integer,ForeignKey('magazine.id'))

magazine_association = Table('magazine_association', Base.metadata,
    Column('magazine_id',Integer,ForeignKey('magazine.id', ondelete='cascade')),
    Column('user_id',Integer,ForeignKey('user.id', ondelete= 'cascade'))
)

I am trying to access all the articles for a particular user. I can iterate over all magazines and get all articles from each magazine that a user owns, like the following

for mag in user.magazines:
    for art in mag.articles:
        return art.title

If the number of articles and magazines is very large, what is the best way to use limit and offset to paginate ? I am having trouble in creating a single query that can get me a collection of articles for a user on which I could paginate. I need to do this since I am trying to expose this as a REST API.

Grateful for any pointers. Thanks

Answer 1

Flask-SQLAlchemy has built in pagination support. See here.