SQLAlchemy Many-to-Many Users, Groups, Roles

[Leslie Luyt]

I am trying to integrate with an existing user-group-role table structure where a user can belong to many groups and have multiple roles on each group.

I found a similar question to this, however it does not allow for multiple roles: Many-to-many declarative SQLAlchemy definition for users, groups, and roles

I have the following table structure and would like to be able to access the roles in the following sort of manner: group.users[0].roles

It would also be nice to be able to access it from the other directions but is not required i.e. user.groups[0].roles or role.groups[0].users





class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)

class Group(Base):
    __tablename__ = 'groups'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(16), unique=True)

class UserGroupRole(Base):
    __tablename__ = 'user_group_role'
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'), nullable=False)
    role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'), nullable=False)

[Mike Bayer]

On Fri, Oct 20, 2017 at 8:53 AM, Leslie Luyt <lesliel...@gmail.com> wrote:
> I am trying to integrate with an existing user-group-role table structure
> where a user can belong to many groups and have multiple roles on each
> group.
>
>
> I found a similar question to this, however it does not allow for multiple
> roles: Many-to-many declarative SQLAlchemy definition for users, groups, and
> roles
>
>
> I have the following table structure and would like to be able to access the
> roles in the following sort of manner: group.users[0].roles
>
> It would also be nice to be able to access it from the other directions but
> is not required i.e. user.groups[0].roles or role.groups[0].users

OK so, the first step would be to build out linkages between the
classes using relationship(). The documentation for this feature
starts at: http://docs.sqlalchemy.org/en/latest/orm/relationships.html

From there, you'd be using the association proxy pattern so you'd want
to focus on the association pattern:
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object
and then to remove the "hop" you'd look into using association proxy:
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects


So those are the areas you'd be looking to use. Now if you've tried
all that and it's not working, provide examples of what you've tried
and we can see if we can find where it's going wrong.

>
>
> class Role(Base):
> __tablename__ = 'roles'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class User(Base):
> __tablename__ = 'users'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class Group(Base):
> __tablename__ = 'groups'
> id = Column(Integer, primary_key=True, autoincrement=True)
> name = Column(Unicode(16), unique=True)
>
> class UserGroupRole(Base):
> __tablename__ = 'user_group_role'
> id = Column(Integer, primary_key=True, autoincrement=True)
> user_id = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'),
> nullable=False)
> group_id = Column(Integer, ForeignKey('groups.id', ondelete='CASCADE'),
> nullable=False)
> role_id = Column(Integer, ForeignKey('roles.id', ondelete='CASCADE'),
> nullable=False)
>
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Leslie Luyt]

Here is my example:

class Role(Base):
    __tablename__ = 'role'
    role_id = Column(BigInteger, primary_key=True)
    role_name = Column(String, nullable=False)
    role_description = Column(String)


class Group(Base):
    __tablename__ = 'group'
    group_id = Column(BigInteger, primary_key=True)
    group_name = Column(String, nullable=False)
    group_description = Column(String)

    profiles = association_proxy('_profile_group_roles', 'profile')


class Profile(Base):
    __tablename__ = 'profile'
    profile_id = Column(BigInteger, primary_key=True)
    profile_username = Column(String, nullable=False, unique=True)

    groups = association_proxy('_profile_group_roles', 'group')
    roles = association_proxy('_profile_group_roles', 'role')


class ProfileGroupRole(Base):
    __tablename__ = 'profile_group_role_assoc'
    profile_id = Column(BigInteger, ForeignKey('profile.profile_id'), primary_key=True)
    group_id = Column(BigInteger, ForeignKey('group.group_id'), primary_key=True)
    role_id = Column(BigInteger, ForeignKey('role.role_id'), primary_key=True)

    group = relationship(Group, backref=backref('_profile_group_roles', cascade='all, delete-orphan'))
    role = relationship(Role)
    profile = relationship(Profile, backref=backref('_profile_group_roles', cascade='all, delete-orphan'))


Example data:
        s = db.connect(engine_config)
        profile1 = db.Profile(profile_username='test_user')
        group1 = db.Group(group_name='group1', group_description='')
        role1 = db.Role(role_name='role1')
        prg1 = db.ProfileGroupRole(profile=profile1, group=group1, role=role1)
        group2 = db.Group(group_name='group2', group_description='')
        role2 = db.Role(role_name='role2')
        prg2 = db.ProfileGroupRole(profile=profile1, group=group2, role=role1)
        prg3 = db.ProfileGroupRole(profile=profile1, group=group2, role=role2)
        s.add_all([prg1, prg2, prg3])
        s.commit()

Querying:
db.Group.query.filter(db.Group.group_name == 'group1').one().profiles[0].roles

produces:
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}, Role{'role_id': 5, 'role_name': 'role2', 'role_description': None}]

and now what I expected to get back was just one the one role associated with 'group1':
[Role{'role_id': 4, 'role_name': 'role1', 'role_description': None}]

[Mike Bayer]

OK so this is a little awkward if you truly want
"group.profiles[0].roles" to be local to the group. It means you don't
really want Group.profiles to be a list of Profile objects, which is
an object that can be associated with multiple groups. You want a
"view" object that is a GroupProfile, a Profile that's associated with
a specific group, which then has one or more Roles set up for that
Group/Profile association. So implied here is that the join across
the ProfileGroupRole association is between GroupProfile and Role - as
far as how to link Group and Profile together we will need to produce
distinct pairs by joining against a DISTINCT of ProfileGroupRole.
This is a more complicated technique.

I'm going off your original email that indicates you can't change the
table structure at all here. So to work all these requirements
together we can create a GroupProfile "view" object as:

class GroupProfile(Base):
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, ProfileGroupRole).join(Profile)).
distinct().alias()
)

group = relationship(
Group,
backref=backref("profiles", viewonly=True,
order_by=__table__.c.profile_id),
primaryjoin=foreign(__table__.c.group_id) == Group.group_id,
viewonly=True)

roles = relationship(
Role,
secondary=ProfileGroupRole.__table__,
primaryjoin=and_(
__table__.c.group_id == ProfileGroupRole.group_id,
__table__.c.profile_id == ProfileGroupRole.profile_id),
viewonly=True,
collection_class=set,
lazy="joined", innerjoin=True)

Just so you know, that mapping is using several advanced techniques to
work the three-way association table into a structure that behaves
more like individual two-way associations (however note this is
strictly read-only stuff, it won't persist data as designed). The map
to a SELECT sets up that we can get unique Group/Profile pairs
regardless of how many ProfileGroupRole rows link them together. The
GroupProfile.role relationship brings ProfileGroupRole in as an
association table a second time to produce individual rows per Role,
and it also uses joined eager loading with innerjoin=True to emit
fewer queries; we can use innerjoin because there are definitely Role
rows present.


Below is full demo for SQLite that returns what you expect as well as
what I think you expect for some more complex cases:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Role(Base):
__tablename__ = 'role'

role_id = Column(Integer, primary_key=True)

role_name = Column(String, nullable=False)
role_description = Column(String)

def __repr__(self):
return "%s%r" % (
self.__class__.__name__,
{k: v for k, v in self.__dict__.items()
if not k.startswith('_')})

class Group(Base):
__tablename__ = 'group'

group_id = Column(Integer, primary_key=True)

group_name = Column(String, nullable=False)
group_description = Column(String)

class Profile(Base):
__tablename__ = 'profile'

profile_id = Column(Integer, primary_key=True)

profile_username = Column(String, nullable=False, unique=True)

class ProfileGroupRole(Base):
__tablename__ = 'profile_group_role_assoc'
profile_id = Column(

Integer, ForeignKey('profile.profile_id'), primary_key=True)
group_id = Column(

Integer, ForeignKey('group.group_id'), primary_key=True)
role_id = Column(

Integer, ForeignKey('role.role_id'), primary_key=True)

group = relationship(
Group, backref=backref('_profile_group_roles',
cascade='all, delete-orphan'))
role = relationship(Role)
profile = relationship(

Profile, backref=backref('_profile_group_roles',
cascade='all, delete-orphan'))

# using generic DISTINCT here, so create a subquery to minimize the columns
# against which DISTINCT is being applied. On Postgresql, we can
# use DISTINCT ON which would allow us to drop the extra subquery.
distinct_profile_groups = select(
[ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\
distinct().alias()


class GroupProfile(Base):
# the GroupProfile itself is then based on Group/Profile rows
# joined to our distinct group_id/profile_id pairs
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, distinct_profile_groups).join(Profile)).
alias()
)

group = relationship(
Group,
backref=backref("profiles", viewonly=True,
order_by=__table__.c.profile_id),
# "foreign" - means our local "group_id" column can refer
# to the same "Group.group_id" across multiple rows, e.g. as though
# it's a foreign key to Group.group_id
primaryjoin=foreign(__table__.c.group_id) == Group.group_id,
viewonly=True)

roles = relationship(
Role,
secondary=ProfileGroupRole.__table__,
primaryjoin=and_(
__table__.c.group_id == ProfileGroupRole.group_id,
__table__.c.profile_id == ProfileGroupRole.profile_id),
viewonly=True,
collection_class=set,
lazy="joined", innerjoin=True)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)


s = Session(e)

profile1 = Profile(profile_username='test_user')
group1 = Group(group_name='group1', group_description='')
role1 = Role(role_name='role1')

prg1 = ProfileGroupRole(profile=profile1, group=group1, role=role1)

group2 = Group(group_name='group2', group_description='')
role2 = Role(role_name='role2')
prg2 = ProfileGroupRole(profile=profile1, group=group2, role=role1)
prg3 = ProfileGroupRole(profile=profile1, group=group2, role=role2)

group3 = Group(group_name='group3', group_description='')
profile2 = Profile(profile_username='user2')
role3 = Role(role_name='role3')

prg4 = ProfileGroupRole(profile=profile1, group=group3, role=role1)
prg5 = ProfileGroupRole(profile=profile2, group=group3, role=role2)
prg6 = ProfileGroupRole(profile=profile2, group=group3, role=role3)

s.add_all([prg1, prg2, prg3, prg4, prg5])
s.commit()


# test one. A group with one GroupProfile and one Role.
group1 = s.query(Group).filter(Group.group_name == 'group1').one()

assert group1.profiles[0].roles == {role1}
print group1.profiles[0].roles

# test two. A group with one GroupProfile and two Roles.
group2 = s.query(Group).filter(Group.group_name == 'group2').one()

assert group2.profiles[0].roles == {role1, role2}
print group2.profiles[0].roles
assert len(group2.profiles) == 1

# test three. A group with *two* GroupProfile objects (e.g. two Profiles),
# each with roles.
group3 = s.query(Group).filter(Group.group_name == 'group3').one()

assert group3.profiles[0].roles == {role1}
assert group3.profiles[1].roles == {role2, role3}
print group3.profiles[0].roles
print group3.profiles[1].roles

assert len(group3.profiles) == 2

[Mike Bayer]

note I inadvertently used a previous form for the first GroupProfile
example. The two forms are:

class GroupProfile(Base):
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, ProfileGroupRole).join(Profile)).
distinct().alias()
)

...

and the potentially more efficient one:

distinct_profile_groups = select(
[ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\
distinct().alias()


class GroupProfile(Base):
# the GroupProfile itself is then based on Group/Profile rows
# joined to our distinct group_id/profile_id pairs
__table__ = (
select([Group.group_id, Profile]).
select_from(join(Group, distinct_profile_groups).join(Profile)).
alias()
)

The second form applies DISTINCT to only two columns, whereas the
first applies DISTINCT to all columns in Profile, which can be
wasteful. A third form could use Postgresql DISTINCT ON to limit the
scope of the DISTINCT without using a second subquery.

[Leslie Luyt]

This is fantastic, I didn't realise you could define a table using a sql expression. I am always amazed by how amazing and configurable SQLAlchemy is.

Thank you so much for the help with this.

PS. I am a huge fan. Please give Dilly a pat for me =).