title: How to left outer join use SQLAlchemy Association Object
body: <p>I have three table roles, pages and role_pages. I try to use Association Class with extra data in association table as in the following link <a href="https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html" rel="nofollow noreferrer">https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html</a>. And this is my model class.</p>
  <pre><code>class RolePage(db.Model):
      __tablename__ = 'role_pages'
      role_id = db.Column(db.Integer, db.ForeignKey('roles.role_id'), primary_key=True)
      page_id = db.Column(db.Integer, db.ForeignKey('pages.page_id'), primary_key=True)
      can_readed = db.Column(db.Boolean, default=False)
      can_created = db.Column(db.Boolean, default=False)
      can_updated = db.Column(db.Boolean, default=False)
      can_deleted = db.Column(db.Boolean, default=False)
      can_submited = db.Column(db.Boolean, default=False)
      can_approved = db.Column(db.Boolean, default=False)
      arole = db.relationship('Role', back_populates='pages')
      apage = db.relationship('Page', back_populates='roles')
  
      def __repr__(self):
          return f'&lt;RolePage ({self.role_id}, {self.page_id}, {self.can_readed}, {self.can_created})&gt;'
  
  
  class Role(db.Model):
      __tablename__ = 'roles'
      role_id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(32), unique=True, nullable=True)
      pages = db.relationship('RolePage', back_populates='arole')
  
      def __repr__(self):
          return f'&lt;Role {self.name}&gt;'
  
  
  class Page(db.Model):
      __tablename__ = 'pages'
      page_id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(32), unique=True, nullable=True)
      roles = db.relationship('RolePage', back_populates='apage')
  
      def __repr__(self):
          return f'&lt;Page {self.name}&gt;'
  </code></pre>
  <p>My problem is, I want to query use left join, like query I make in sql:</p>
  <pre><code>select * from pages p
  left join role_pages rp on rp.page_id = p.page_id and rp.role_id = 2 
  left join roles r on r.role_id = rp.role_id;
  </code></pre>
  <p>I try to query <code>left join</code> table use ORM.</p>
  <pre><code>Page.query\
      .outerjoin(RolePage, and_(RolePage.role_id==id))\
      .all()
  </code></pre>
  <p>But the <code>left join</code> not work. What the best way to use <code>left join</code> like my problem in sqlalchemy.</p>
  <p>Thanks.</p>