Flask sqlalchemy filter objects in relationship for each object

How to filter objects in relationship in one command?

Example filter: I have list of childrens and every children has toys. Show me how to filter each childs toys, so that list child.toys contains only red toys.

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(40))
    toys = db.relationship('Toy', lazy='dynamic')


class Toy(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    color = db.Column(db.String(40))
    child_id = db.Column(db.Integer, db.ForeignKey('child.id') )

Child id	Child name
1	First
2	Second

Toy id	Toy color	Toy child_id
1	Blue	1
2	Red	1
3	Orange	2
4	Red	2

Desired output in python list:

Child id	Child name	Toy id	Toy color
1	First	2	Red
2	Second	4	Red

Edit: this table will be printed by:

for child in filtered_children: 
    for toy in child.toys:
        print(f'{child.id} {child.name} {toy.id} {toy.color}')

Dynamic Relationship Loaders provides correct result, but you have to iterate like this in for loop:

children = Child.query.all()
for child in children:
    child.toys = child.toys.filter_by(color='Red')
len( children[0].toys ) #should by one
len( children[1].toys ) #should by one

Is there a way how to filter objects from relationship without for loop?

Edit: Reformulated question: Is there a way to apply the filter at the outer query such that no additional filtering need to be done inside the for loop such that each child.toys attribute for each child in the loop will only contain Red toys?

Solution

_{This answer has been updated for SQLAlchemy 2.0 to reflect the 2.0 style while maintaining the original intent of this prior revision which was written in the 1.x style.}

Since it appears you already configured some rudimentary relationship that allowed the usage of join conditions, the next step is simply to actually use joins to bring the relationships that were defined together.

My example below uses SQLAlchemy directly, so you may need to adapt the references to those specific to Flask-SQLALchemy (e.g. instead of creating a session from the engine context, you may instead use db.session as per the guidance provided via its quickstart; also the Child and Toy classes I used inherit from a common local Base class for the same reason, otherwise the same principles introduced below should be commonly applicable).

First, import and set up the classes - to keep this generalized, I will be using sqlalchemy directly as noted:

from sqlalchemy import ForeignKey, String
from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session
from sqlalchemy.orm import mapped_column, relationship
from sqlalchemy.orm import contains_eager

class Base(DeclarativeBase):
    pass

class Child(Base):
    __tablename__ = 'child'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(40))
    toys = relationship('Toy')

class Toy(Base):
    __tablename__ = 'toy'
    id: Mapped[int] = mapped_column(primary_key=True)
    color: Mapped[str] = mapped_column(String(40))
    child_id = mapped_column(ForeignKey('child.id'))

Note that the Child.toys relationship construct does not need any other extra arguments, given that the specified 'dynamic' style of loading is incompatible with the query desired.

Then set up the engine and add your data provided from your question:

children = [line.split()[:2] for line in """
1   First
2   Second
""".splitlines() if line.strip()]

toys = [line.split()[:3] for line in """
1   Blue     1
2   Red      1
3   Orange   2
4   Red      2
""".splitlines() if line.strip()]

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all(
        [Child(id=int(i), name=j) for i, j in children])
    session.add_all(
        [Toy(id=int(i), color=j, child_id=int(k)) for i, j, k in toys])
    session.commit()

With the data added, we can see that in the documentation for loading relationships from SQLAlchemy, there is a rather comprehensive set of relationship loading techniques. As the amount of data being dealt with is small and to keep the example straightforward, the contains_eager from the list of relationship loader API may be suitable one for your use case, as it states "that the given attribute should be eagerly loaded from columns stated manually in the query." Moreover, as per the example provided with the documentation for this loader, it was demonstrated that the collection defined by the relationship will contain only the filtered entries, and not any other entries that are in fact associated with the collection (for another explanation, see this answer).

So let's see it in action:

with Session(engine) as session:
    query = (select(Child)
        .join(Child.toys)
        .filter(Toy.color == 'Red')
        .options(contains_eager(Child.toys))
    )
    filtered_children = session.scalars(query).unique().all()

This query ensures that the toys relationship declared through the Child is joined with the query, and that we also filter by "Red" toys, with the option to indicate that Child.toys should be "eager loaded from columns stated manually in the query" such that it will become available through each of the returned child object, without needing additional selects triggered on-demand.

Now, see that your most recent desired for loop over filtered_children and their toys produce your desired output:

for child in filtered_children:
    for toy in child.toys:
        print(f'{child.id} {child.name} {toy.id} {toy.color}')

The following output should be produced (gist of all the code):

1 First 2 Red
2 Second 4 Red

If we had logging enabled, we will see that the following output that indicates the SELECT statement that was issued by SQLAlchemy's engine:

INFO:sqlalchemy.engine.Engine:SELECT toy.id, toy.color, toy.child_id, child.id AS id_1, child.name 
FROM child JOIN toy ON child.id = toy.child_id 
WHERE toy.color = ?
INFO:sqlalchemy.engine.Engine:[generated in 0.00014s] ('Red',)
DEBUG:sqlalchemy.engine.Engine:Col ('id', 'color', 'child_id', 'id_1', 'name')
DEBUG:sqlalchemy.engine.Engine:Row (2, 'Red', 1, 1, 'First')
DEBUG:sqlalchemy.engine.Engine:Row (4, 'Red', 2, 2, 'Second')

Note that only a single SELECT ... JOIN query was done and no additional queries were made for each of the Child. While this may appear to avoid perceived performance impact of having to do manual queries later (especially being only loader that will ensure the relationships have the filter applied eagerly), this can have real impact if there are many columns in both Child and Toy and that many instances are returned, as this strategy effectively forces a Cartesian product and may potentially result in more than expected amount of data being passed from the database to the application, so this is something to be mindful of.