librelist archives

Flask-SQLAlchemy: Multilevel Relationships

From:

Christoph Erdle

Date:

2011-07-27 @ 17:29

Hi,

I'm trying to implement an in my view simple authorization scheme: users 
and groups get access to environments, the access can be granted on user 
and on group level, users can be members of groups. So far I implemented 
the following models and mappingtables using Flask-SQLAlchemy. Is there a 
way to "chain" the relationships, e.g. query the user's groups' 
environments? I thought about something like 
current_user().groups.environments (current_user() gets me the currently 
logged in user object user certificate based authentication), but then i 
get back the error:

AttributeError: 'InstrumentedList' object has no attribute 'environments'

Getting the user's groups works perfectly, also getting a user's 
environments and a group's environment. What's missing is the part, where 
I get the environments the user is "inheriting" through his group 
memberships. Likely I'm missing some kind of "magic" to link the models 
together with (Flask-)SQLAlchemy, or is this only possible using direct 
SQL queries joining the relevant tables together circumventing the ORM?

Any input is highly appreciated.

Kind regards,
Chris

Model definition:
------

class Environment(db.Model):
    __tablename__ = 'environment'
    id = db.Column(db.Integer, primary_key=True)
    fs_rel_path = db.Column(db.String, nullable=False)
    active = db.Column(db.Boolean, nullable=False)
    display_name = db.Column(db.String, nullable=False)
    users = db.relationship('User', secondary=userenvironmentmapping, 
backref=db.backref('environments', lazy='joined'), lazy='joined')
    groups = db.relationship('Group', secondary=groupenvironmentmapping, 
backref=db.backref('environments', lazy='joined'), lazy='joined')
    
    def __init__(self, fs_rel_path=None, display_name=None, active=True):
        self.fs_rel_path = fs_rel_path
        self.display_name = display_name
        self.active = active
        
    def __repr__(self):
        return self.display_name

class Group(db.Model):
    __tablename__ = 'group'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)
    admin = db.Column(db.Boolean, nullable=False)
    #members = db.relationship('User', secondary=usergroupmembership, 
backref='groups', lazy='dynamic')
    
    def __init__(self, name=None, admin=False):
        self.name = name
        self.admin = admin
        
    def __repr__(self):
        return self.name

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    cert_dn = db.Column(db.String, nullable=False, index=True)
    admin = db.Column(db.Boolean, nullable=False)
    groups = db.relationship('Group', secondary=usergroupmembership, 
backref=db.backref('users', lazy='joined'), lazy='joined')
                            
    def __init__(self, cert_dn=None, admin=False):
        self.cert_dn = cert_dn
        self.admin = admin
        
    def __repr__(self):
        return self.cert_dn

usergroupmembership = db.Table('user_group_membership',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id', 
onupdate="CASCADE", ondelete="CASCADE"), index=True),
    db.Column('group_id', db.Integer, db.ForeignKey('group.id', 
onupdate="CASCADE", ondelete="CASCADE"), index=True)
)

userenvironmentmapping = db.Table('user_environment_mapping',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id', 
onupdate="CASCADE", ondelete="CASCADE"), index=True),
    db.Column('environment_id', db.Integer, 
db.ForeignKey('environment.id', onupdate="CASCADE", ondelete="CASCADE"), 
index=True)
)

groupenvironmentmapping = db.Table('group_environment_mapping',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('group_id', db.Integer, db.ForeignKey('group.id', 
onupdate="CASCADE", ondelete="CASCADE"), index=True),
    db.Column('environment_id', db.Integer, 
db.ForeignKey('environment.id', onupdate="CASCADE", ondelete="CASCADE"), 
index=True)
)