title: SQLAlchemy eager loading recursive model
body: <p>How can you write the model that it eager loads the parents and children of a certain role recursively. So not only the child of the role you are fetching now but also it's children.</p>
  
  <p>Do you risk ending in an infinite loop or does SQLAlchemy have the logic to detect these?</p>
  
  <p>The SQLAlchemy model is as follows:</p>
  
  <pre><code>from sqlalchemy.ext.declarative import declarative_base
  
  
  roles_parents = Table(
  'roles_parents', Base.metadata,
  Column('role_id', Integer, ForeignKey('roles.id')),
  Column('parent_id', Integer, ForeignKey('roles.id'))
  )
  
  
  Base = declarative_base()
  class Role(Base):
  
      __tablename__ = 'roles'
  
  id = Column(Integer, primary_key=True)
  name = Column(String(20))
  parents = relationship(
      'Role',
      secondary=roles_parents,
      primaryjoin=(id == roles_parents.c.role_id),
      secondaryjoin=(id == roles_parents.c.parent_id),
      backref=backref('children', lazy='joined'),
      lazy='joined'
  )
  
  def get_children(self):
      logger.log_dbg("get_children(self) with name: "  + self.name)
      for child in self.children:
          yield child
          for grandchild in child.get_children():
              yield grandchild
  
  @staticmethod
  def get_by_name(name):
      logger.log_dbg("get_by_name( " + name + " )")
      with DBManager().session_scope() as session:
          role = session.query(Role).options(joinedload(
              Role.children).joinedload(Role.parents)).filter_by(
              name=name).first()
          # role = session.query(Role).filter_by(name=name).first()
          session.expunge_all()
          return role
  </code></pre>
  
  <p>You can see that I tried to enable eager loading on the parents relationship via an attribute in the relationship and via options in the query where I fetch a Role.</p>
  
  <p>The reason for needing this eager load (and session.expunge_all()), is that the session is lost when trying to get the child via get_children().</p>
  
  <p>Due to the eager load, get_children no longer fails when accessing the child role of this role. However, it still fails when trying to fetch the grandchild. So eager loading seems to work for the child role but does not eager load its children.</p>