Dear experts,

I have two tables (Ticket, User) joined by a secondary Ticket_User_Map 
mapping table. The mapping table contains an additional attribute, 
'user_role', which defines the relationship of the user to the support 
ticket, e.g. 'assigned' or 'watcher'.

Using SQLAlchemy, I want to add a 'users' collection to a Ticket object in 
the form of a dictionary of lists, where the dictionary is keyed on the 
'user_role' attribute of the secondary table.

For example:

class User(Base):
    __tablename__ = 'user'
    id = Column(BigInteger, primary_key=True)
    username = Column(String(100))

class Ticket(Base):
    __tablename__ = 'ticket'
    id = Column(BigInteger, primary_key=True)
    description = Column(String(1000))

class TicketUserMap(Base):
    __tablename__ = 'ticket_user_map'
    id = Column(BigInteger, primary_key=True)
    ticket_id = Column(ForeignKey(''), nullable=False, index=True)
    user_id = Column(ForeignKey(''), nullable=False, index=True)
    user_role = Column(String(100), nullable=False)

Given the classes above, I want to define a SQLAlchemy relationship between 
Ticket and User to give me the following:

>>> ticket.users
    'watcher': [
    'assigned': [

Also, adding objects to the collection or deleting items should 
automatically update the secondary mapping table, e.g.:

>>> ticket.users['watcher'].append(User('matt'))

Is there an elegant way in which SQLAlchemy can handle this?  E.g. using a 
customised MappedCollection and/or Association Proxy?

Any advice or suggestions will be much appreciated.

SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See 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 post to this group, send email to
Visit this group at
For more options, visit

Reply via email to