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

Question

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:

• Sometimes User.posts can be sliced to avoid eagerloading of the large set of posts written by heavy users.
• However usually User.posts should don’t produce 1+N queries.

Answer 1

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")