On Fri, Oct 20, 2017 at 8:53 AM, Leslie Luyt <leslieluyt.j...@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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
