CODEWITHSUNDEEP
pythonsqlalchemy
minhee
minhee

Reputation: 5818

How to use joinedload/contains_eager for query-enabled relationships (lazy='dynamic' option) in SQLAlchemy

I have the following model classes declared by SQLAlchemy:

class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    created_at = Colmn(DateTime, nullable=False, default=func.now())

class Post(Base):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)
    user = relationship(User, backref=backref('posts', lazy='dynamic'))
    title = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    created_at = Colmn(DateTime, nullable=False, default=func.now())

As I quoted, these models have a relationship and its backref named posts set to be query-enabled (through lazy='dynamic' option). Because some users may have the large set of posts while most users don’t.

With these models, I tried joinedload for User.posts, but I faced the error:

>>> users = session.query(User).options(joinedload(User.posts))[:30]
Traceback (most recent call last):
  ...
InvalidRequestError: 'User.posts' does not support object population - eager loading cannot be applied.

Is there any way to work around this situation? I need following two functionalities both:

Upvotes: 9

Views: 8647

Answers (1)

SingleNegationElimination
SingleNegationElimination

Reputation: 156148

The problem is that the property on User for posts is a dynamic relationship; It's supposed to return a Query object. There's no way for the property to know, or safely communicate, that this time, all of the related items are already loaded.

The simple workaround will be to have two properties, one that uses the normal lazy loading behavior (that you can set to eager load for specific queries where it makes sense), and another that always returns a dynamic relationship.

class User(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, unique=True)
    created_at = Colmn(DateTime, nullable=False, default=func.now())

class Post(Base):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)
    user = relationship(User, backref=backref('posts'))
    title = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    created_at = Colmn(DateTime, nullable=False, default=func.now())

User.post_query = relationship(Post, lazy="dynamic")

Upvotes: 17

Related Questions