CODEWITHSUNDEEP
databasesqlalchemy
Lucian Boca
Lucian Boca

Reputation: 467

SQLAlchemy query a chain of One-to-Many relationships

I'm looking for a way to query a chain of one-to-many relationships in SQLAlchemy. Given the following models (Base is a declarative base):

class Book(Base):
    id = Column(Integer, primary_key=True)
    chapters = relationship("Chapter", backref="book")

class Chapter(Base):
    id = Column(Integer, primary_key=True)
    book_id = Column(Integer, ForeignKey(Book.id))
    pages = relationship("Page", backref="chapter", lazy="dynamic")

class Page(Base):
    id = Column(Integer, primary_key=True)
    chapter_id = Column(Integer, ForeignKey(Chapter.id))

I need to query the Page entities by the Book they are associated with (through the Chapter). However,

Page.query.filter(Page.chapter.book_id == 1)

doesn't work in this case. The number of Pages is quite big (hence the lazy="dynamic" parameter when querying Chapters), and I'd like not to de-normalize by adding a book_id column to the Page table.

Upvotes: 0

Views: 3931

Answers (1)

van
van

Reputation: 76992

qry = Page.query.join(Chapter).join(Book).filter(Book.id == 1)

Upvotes: 1

Related Questions