Flask sqlalchemy filter objects in relationship for each object

user_4233474 0
Viewed 962

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?

sqlalchemy python
1 Answers

Since it appears you already configured some rudimentary relationship that allowed the usage of join conditions, the next step is simply to actually use the Query.join() call.

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 you may instead use db.session, which I referenced from the Quickstart; also the Child and Toy classes I used inherit from a common sqlalchemy.ext.declarative.declarative_base() 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 Column, Integer, String
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, contains_eager

Base = declarative_base()

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    name = Column(String(40))
    toys = relationship('Toy')

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

Note that we have removed the extra keyword arguments for the Child.toys relationship construct, as 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:

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(Child(name='First'))
session.add(Child(name='Second'))
session.add(Toy(color='Blue', child_id=1))
session.add(Toy(color='Red', child_id=1))
session.add(Toy(color='Orange', child_id=2))
session.add(Toy(color='Red', child_id=2))
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 API, and the contains_eager is the suitable one for your use case, as it states "that the given attribute should be eagerly loaded from columns stated manually in the query." So let's see it in action:

filtered_children = session.query(Child).join(Child.toys).filter(
    Toy.color=='Red'
).options(
    contains_eager(Child.toys)
).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.

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:

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.base.Engine:SELECT toy.id AS toy_id, toy.color AS toy_color, toy.child_id AS toy_child_id, child.id AS child_id, child.name AS child_name 
FROM child JOIN toy ON child.id = toy.child_id 
WHERE toy.color = ?
INFO:sqlalchemy.engine.base.Engine:('Red',)
DEBUG:sqlalchemy.engine.base.Engine:Col ('toy_id', 'toy_color', 'toy_child_id', 'child_id', 'child_name')
DEBUG:sqlalchemy.engine.base.Engine:Row (2, 'Red', 1, 1, 'First')
DEBUG:sqlalchemy.engine.base.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, which would result in significant performance impact.

user_29048960
Related