CODEWITHSUNDEEP
pythonsqlalchemy
melchoir55
melchoir55

Reputation: 7276

avoid loading specific class in hierarchy with sqlalchemy query

I want to load an entity via a sqlalchemy query while explicitly avoiding loading a specific class of entity as a field on any instance of any child of my loaded entity. Take the below data model:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship

base = declarative_base()


class Parent(base):
    __tablename__ = 'Parent'
    uid = Column(Integer, primary_key=True)


class Child(base):
    __tablename__ = 'Child'
    uid = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('Parent.uid'))
    parent = relationship('Parent', backref="children")


class OtherChild(base):
    __tablename__ = 'OtherChild'
    uid = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('Parent.uid'))
    parent = relationship('Parent', backref="other_children")


class Bicycle(base):
    __tablename__ = 'Bicycle'
    uid = Column(Integer, primary_key=True)

    child_id = Column(Integer, ForeignKey('Child.uid'))
    child = relationship('Child', backref="bicycles")

    child_id = Column(Integer, ForeignKey('OtherChild.uid'))
    child = relationship('OtherChild', backref="bicycles")

If I do a Parent.query.all() then I'm going to get back any Child or OtherChild objects which are in those Parent objects in the children and other_children fields, respectively. Further, I'll get any Bicycle objects which are embedded inside either the Child or OtherChild objects.

I wish to do a query on Parent which explicitly avoids loading any Bicycle objects on any children regardless how deep they may be in the data structure.

Update:

It is possible to constrain the children returned in a query using options(contains_eager(<pathtoclass>)). For example (not tested but pretty sure it would work):

query = query.outerjoin(Child, primaryjoin.expression).\
    options(contains_eager('children'))

However, this requires explicitly describing the options for each path. In a circumstance with hundreds of valid options this becomes onerous. I would prefer to just express something like query.contains_eager(CLASS).

Upvotes: 2

Views: 482

Answers (1)

Brian Popeck
Brian Popeck

Reputation: 139

According to the docs, relationships use lazy loading by default. No Bicycle objects will load until you directly access the relationship attribute.

Since the relationships appear to be unidirectional from Bicycle to Child and from Bicycle to OtherChild, you shouldn't have to worry about loading Bicycle objects unless you specifically query for them.

Upvotes: 1

Related Questions