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('ticket.id'), nullable=False, index=True)
user_id = Column(ForeignKey('user.id'), 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': [
User('bob'),
User('joe')
],
'assigned': [
User('sue'),
User('sam')
]
}
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
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
