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?

Patrimony

Why not query the Chapter table for book.id? That will only take one query – Halbeib

Note that this is a simplified case: it may very well be that initiating a second, independent query is the quickest/best way at this information. But a) The original object was checked for various security concerns and b) It may already have that information in-cache, and I'm not sure if a separate query would by necessity bypass any caching of the original object. – Patrimony

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.

Hardhack

Answer 2

V2.x compatible code of the accepted answer:

from sqlalchemy import select
from sqlalchemy.orm import with_parent
...

book = ...  # get book instance 

with Session(engine) as session:
    stmt = select(Chapter)
           .where(
               and_(
                   with_parent(book, Book.chapters),
                   Chapter.name == "57th Arabian Tale"
               )
           )
    chapter = session.scalars(stmt).one()

Mckellar