Note
SQLAlchemy Query for Child Objects in One to Many Self Referential Relationship
Jul 06, 2022
Finding the answer to a rather simple question proved to be shockingly
difficult.
How to query such that parent objects include all child objects in a one to
many relationship using SQLAlchemy?
Here’s a contrived example:
...
Finding the answer to a rather simple question proved to be shockingly
difficult.
How to query such that parent objects include all child objects in a one to
many relationship using SQLAlchemy?
Here’s a contrived example:
class ProductTest(Base):
__tablename__ = 'products_test'
product_id=Column(BigInteger, autoincrement=True, primary_key=True)
parent_variant = Column(BigInteger, ForeignKey('products_test.product_id'))
price=Column('price', Float, default=0.0)
product=Column('product', String(255))
variants = relationship('ProductTest', lazy="joined", join_depth=2)
The important part is this:
variants = relationship('ProductTest', lazy="joined", join_depth=2)
Notice the lazy and join_depth attributes.