Saturday, 22 May 2021

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?



from Flask sqlalchemy filter objects in relationship for each object

No comments:

Post a Comment