Consulta muchos a muchos en SQLAlchemy

Tengo los siguientes modelos. Un usuario tiene muchos roles, y un rol puede tener muchos permisos. No sé cómo hacer una consulta para obtener lo que quiero.

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

Quiero obtener una lista (preferiblemente única) de todos los permisos asignados a un usuario, pero parece que no puedo encontrar la manera de hacerlo.

Puedo obtener la lista creando un generador en el modelo de usuario:

 def get_all_permissions(self): for role in self.roles: for perm in role.permissions: yield perm 

Pero me encantaría poder hacerlo en una sola consulta.

Es posible que sus tablas de asociación no se reconozcan correctamente, ya que no especificó el parámetro de metadatos. Este script funciona para mí:

 #!/bin/python from sqlalchemy import Table from sqlalchemy import Integer, String, ForeignKey, create_engine, Column, PrimaryKeyConstraint from sqlalchemy.orm import relationship, backref, sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base() user_role = Table( 'user_role', Base.metadata, Column('user_id', Integer, ForeignKey('users.id')), Column('role_id', Integer, ForeignKey('roles.id')), PrimaryKeyConstraint('user_id', 'role_id') ) role_permission = Table( 'role_permission', Base.metadata, Column('permission_id', Integer, ForeignKey('permissions.id')), Column('role_id', Integer, ForeignKey('roles.id')), PrimaryKeyConstraint('permission_id', 'role_id') ) class Role(Base): __tablename__ = 'roles' id = Column(Integer, primary_key=True) name = Column(String(100), unique=True, nullable=False) class Permission(Base): __tablename__ = 'permissions' id = Column(Integer, primary_key=True) name = Column(String(100), nullable=False) roles = relationship("Role", secondary=role_permission, backref=backref('permissions')) class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) username = Column(String(60), unique=True, nullable=False) password_hash = Column(String(80), nullable=False) roles = relationship("Role", secondary=user_role, backref=backref('users')) Base.metadata.create_all(engine) session = sessionmaker(bind=engine)() u = User(username="user", password_hash="secret") r1 = Role(name="Role 1") session.add(r1) r2 = Role(name="Role 2") session.add(r2) p1 = Permission(name="Permission 1") p2 = Permission(name="Permission 2") p3 = Permission(name="Permission 3") r1.permissions.append(p1) r1.permissions.append(p2) r2.permissions.append(p2) r2.permissions.append(p3) u.roles.append(r1) u.roles.append(r2) session.add(u) for perm in session.query(Permission).join(Role, Permission.roles).\ join(User, Role.users).filter(User.username=="user").distict()all(): print(perm.name) 

Si ya ha cargado el objeto Usuario en la memoria junto con Permisos y roles, el código debe hacerlo rápidamente y sin tener que ir a una base de datos.

De lo contrario, esta consulta debería funcionar:

  user_id = 789 permissions = (db.session.query(Permission) .join(Role, Permission.roles) .join(User, Role.users) .filter(User.id == user_id) ).distinct() #print(permissions) for perm in permissions: print(perm) 

Bueno, para obtener una lista de los permisos, intente algo como esto:

 permissions = session.query(Permission).\ join(Role).join(User).filter(User.username='MisterX').all() 

O filtra por lo que quieras. Para hacer los Permisos únicos, puede usar el grupo por:

 permissions = session.query(Permission.id, Permission.name).join(Role).join(User).\ filter(User.username='MisterX').group_by(Permission.id).all() 

O, sin una consulta especial, use la extensión declarativa, si puede:

 permissions = User.roles.permissions 

¿Eso ayuda?