title: Query many-to-many in SQLAlchemy
body: <p>I have the following models. A user has many roles, and a role can have many permissions. I can't quite figure out how to query to get what I want.</p>
  
  <pre><code>user_role = db.Table(
      'user_role',
      db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
      db.Column('role_id', db.Integer, db.ForeignKey('role.id')),
      db.UniqueConstraint('user_id', 'role_id')
  )
  
  role_permission = db.Table(
      'role_permission', 
      db.Column('permission_id', db.Integer, db.ForeignKey('permission.id')),
      db.Column('role_id', db.Integer, db.ForeignKey('role.id')),
      db.UniqueConstraint('permission_id', 'role_id')
  )
  
  class Role(Base):
      __tablename__ = 'role'
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(100), unique=True, nullable=False)
  
  class Permission(Base):
      __tablename__ = 'permission'
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(100), nullable=False)
  
      roles = db.relation(Role, secondary=role_permission, backref=db.backref('permissions'))
  
  class User(Base, UserMixin):
      __tablename__ = 'user'
  
      id = db.Column(db.Integer, primary_key=True)
      username = db.Column(db.String(60), unique=True, nullable=False)
      password_hash = db.Column(db.String(80), nullable=False)
  
      roles = db.relation(Role, secondary=user_role, backref=db.backref('users'))
  </code></pre>
  
  <p>I want to get a (preferably unique) list of all the permissions assigned to a user, but I can't seem to figure out how to do it.</p>
  
  <p>I can get the list by creating a generator in the User model:</p>
  
  <pre><code>def get_all_permissions(self):
      for role in self.roles:
          for perm in role.permissions:
              yield perm
  </code></pre>
  
  <p>But I'd love to be able to do it in one query.</p>