CODEWITHSUNDEEP
pythonpython-3.xsqlalchemy
Nathaniel Ford
Nathaniel Ford

Reputation: 21220

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

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?

Upvotes: 6

Views: 5028

Answers (2)

Vahidrk
Vahidrk

Reputation: 1186

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()

Upvotes: 0

van
van

Reputation: 76992

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.

Upvotes: 6

Related Questions