CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Syed Mohammad Hosseini
Syed Mohammad Hosseini

Reputation: 522

Flask SQLAlchemy query: filter records from self foreign key relationship

I have a model class with self ForeignKey relation like this:

class Foo(db.Model):
    id = Column(db.Integer, primary_key=True)
    name = Column(db.String(320))
    status = Column(db.Integer) # 0: undone, 1:done
    parent_id = Column(db.Integer, db.ForeignKey('foo.id'), index=True)
    parent = db.relationship(lambda: Foo, remote_side=id, backref='sub_foo')

I need to filter rows that have either no child or all done (status == 1) children. In other words I need to exclude rows that have children with undone (status == 0) status.

Upvotes: -1

Views: 447

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

The easiest way to perform EXISTS queries is to use the any() and has() methods of relationships:

# Note the use of ~ operator for NOT
Foo.query.filter(~Foo.sub_foo.any(status=0))

any() accepts either SQL boolean expressions as positional arguments, or keyword arguments as shorthands for simple equality comparisons.

Upvotes: 2

Related Questions