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

Viewed 7873

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.
1 Answers
Related