How do you query a one-to-many relationship in an SQLAlchemy object instance?

Question

Suppose I have the following (in Python 3 and SQLAlchemy):

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

class Chapter(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)
    book_id = Column(Integer, ForeignKey(Book.id))

def check_for_chapter(book): 
    # This is where I want to check to see if the book has a specific chapter.
    for chapter in book.chapters:
        if chapter.name == "57th Arabian Tale"
            return chapter
    return None

This feels like a 'non-idiomatic' approach, because it seems unlikely to leverage the database to search for the given chapter. In the worst case, it seems like n calls to the db will be made to check for chapter titles, though my limited understanding of SQLAlchemy suggests this can be configured around. What I don't know is if there is a way to initiate a query directly against only the relation of an object you've already fetched? If so, how does one do that?

Answer 1

If you would like to get a specific chapter of a specific book, the code below should do it in one SQL statement:

book = ...  # get book instance 

chapter = (
    session.query(Chapter)
    .with_parent(book)
    .filter(Chapter.name == "57th Arabian Tale")
    .one()
)

If, for example, you have only book title and chapter title, you could do this:

chapter = (
    session.query(Chapter)
    .join(Book)
    .filter(Book.name == "One Thousand and One Nights")
    .filter(Chapter.name == "57th Arabian Tale")
    .one()
)

Also read Querying with Joins and the rest of SQLAlchemy Documentation.