Hey Mike, thank you for the proof of concept! I tried it out and it seems to work. However, as you said, it's a little akward...
I'm not a specialist with DB Design, but I think this pattern is used at other places to. What would your recommondation be for this kind of relationship between tables? As you said, database-wise this is completly reasonable. But in the end I wan't a design which is readable and maintainable in the long term. Btw: Of course, the `is_guide` column should be a boolean, I just forgot to change it from the example code. And I think a AssociationSet would be a better fit, because a member can either guide a event or participate a event, but not both on the same time. Am Samstag, 18. März 2017 03:50:22 UTC+1 schrieb Mike Bayer: > > > > On 03/17/2017 07:43 PM, Jürg Rast wrote: > > I followed the examples about association proxies > > ( > http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html) > > > but now I'm stuck. > > > > I have the following example (using flask + sqlalchemy) of a simple > > event management: > > > > | > > from sqlalchemy import Column, String, Integer, ForeignKey, Boolean > > from sqlalchemy.orm import relationship, backref > > from sqlalchemy.ext.associationproxy import association_proxy > > from . import db, BaseModel > > > > > > class Event(BaseModel): > > __tablename__ = 'event' > > title = Column(String(64)) > > > > # association proxy of "event_participations" collection > > # to "keyword" attribute > > guides = association_proxy('event_participations', 'member', > > creator=lambda m: EventParticipation(member=m, > > is_guide=True)) > > > > participants = association_proxy('event_participations', 'member', > > creator=lambda m: EventParticipation(member=m, > > is_guide=False)) > > > > def __init__(self, name): > > self.title = name > > > > def __repr__(self): > > return 'Event(%s)' % self.title > > > > > > class EventParticipation(db.Model): > > __tablename__ = 'event_participation' > > event_id = Column(Integer, ForeignKey('event.id'), > primary_key=True) > > member_id = Column(Integer, ForeignKey('member.id'), > primary_key=True) > > is_guide = Column(String(50)) > > > > # bidirectional attribute/collection of > "event"/"event_participations" > > event = relationship(Event, > > backref=backref("event_participations", > > cascade="all, delete-orphan") > > ) > > > > # reference to the "Keyword" object > > member = relationship("Member") > > > > def __init__(self, member=None, event=None, is_guide=None): > > self.event = event > > self.member = member > > self.is_guide = is_guide > > > > def __repr__(self): > > return 'EventParticipation(%s, %s, %s)' % (self.event, > > self.member, self.is_guide) > > > > > > class Member(BaseModel): > > __tablename__ = 'member' > > name = Column(String(64)) > > > > def __repr__(self): > > return 'Member(%s)' % repr(self.name) > > > > | > > > > So, basically a member can be a 'guide' for a event or a 'participant' > > and each event can have multiple guides and participants. Therefore I > > created a secondary table which holds the event and member id and also a > > field which indicates if the member has registered as member or as > guide. > > OK so database-wise this is fine, you have Event -> EParticipation(Guide > or Member) -> Member and that's fine (though seems like "is_guide" > should be a Boolean, probably). > > Where it's going wrong is how the association proxies are being used. > You have the is_guide=True|False thing inside the "creator", which is > fine, but that only affects what happens when you append a record, it > doesn't affect what the association proxy does when you read it. so > both of these association proxies are giving you the same list of objects. > > The use case of filtering on read is a little awkward and we have to use > custom collection classes. Making it a little worse is that the > association proxy's system for using custom collection classes is even > more awkward. so below is a full proof of concept. > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.ext import associationproxy > from sqlalchemy.ext.associationproxy import association_proxy > import functools > > Base = declarative_base() > > > class FilteredList(associationproxy._AssociationList): > def __init__( > self, is_guide, lazy_collection, creator, value_attr, > parent): > # awkward. > getter, setter = parent._default_getset( > associationproxy._AssociationList) > super(FilteredList, self).__init__( > lazy_collection, creator, getter, setter, parent > ) > self.is_guide = is_guide > > # define all the "read" methods that need to do filtering here. The > # proxied collection is in self.col > def __iter__(self): > return (m.member for m in self.col if m.is_guide is > self.is_guide) > > def __contains__(self, other): > return other in [ > m.member for m in self.col if m.is_guide is self.is_guide > ] > > > class Event(Base): > __tablename__ = 'event' > id = Column(Integer, primary_key=True) > title = Column(String(64)) > > # association proxy of "event_participations" collection > # to "keyword" attribute > guides = association_proxy( > 'event_participations', 'member', > proxy_factory=functools.partial(FilteredList, True), > creator=lambda m: EventParticipation(member=m, is_guide=True)) > > participants = association_proxy( > 'event_participations', 'member', > proxy_factory=functools.partial(FilteredList, False), > creator=lambda m: EventParticipation(member=m, is_guide=False)) > > def __init__(self, name): > self.title = name > > def __repr__(self): > return 'Event(%s)' % self.title > > > class EventParticipation(Base): > __tablename__ = 'event_participation' > event_id = Column(Integer, ForeignKey('event.id'), primary_key=True) > member_id = Column(Integer, ForeignKey('member.id'), > primary_key=True) > is_guide = Column(Boolean) > > # bidirectional attribute/collection of > "event"/"event_participations" > event = relationship( > Event, > backref=backref("event_participations", > cascade="all, delete-orphan") > ) > > # reference to the "Keyword" object > member = relationship("Member") > > def __init__(self, member=None, event=None, is_guide=None): > self.event = event > self.member = member > self.is_guide = is_guide > > def __repr__(self): > return 'EventParticipation(%s, %s, %s)' % ( > self.event, self.member, self.is_guide) > > > class Member(Base): > __tablename__ = 'member' > id = Column(Integer, primary_key=True) > name = Column(String(64)) > > def __repr__(self): > return 'Member(%s)' % repr(self.name) > > e = create_engine("sqlite://", echo=True) > Base.metadata.create_all(e) > > s = Session(e) > > event = Event('test') > for g in (Member(name='g1'), Member(name='g2')): > event.guides.append(g) > > s.add(event) > s.commit() > > print "-----------------------" > assert g in event.guides > > for m in (Member(name='m1'), Member(name='m2')): > event.participants.append(m) > > s.commit() > > for p in s.query(EventParticipation).all(): > print(p) > > assert m in event.participants > assert g not in event.participants > > > > > > > > Adding guides and participants works as expected, but querying for the > > guides or participants only fails. Eg. in the following example the last > > test fails > > | > > event = Event('test') > > for g in (Member(name='g1'), Member(name='g2')): > > event.guides.append(g) > > > > db.session.add(event) > > db.session.commit() > > > > assert g in event.guides > > > > for m in (Member(name='m1'), Member(name='m2')): > > event.participants.append(m) > > > > db.session.commit() > > > > for p in EventParticipation.query.all(): > > print(p) > > > > assert m in event.participants > > assert g not in event.participants > > | > > > > How can I specify the filter for the association proxy? Is this possible > > or is this database design just not good and I should change the data > > layout (If yes, what would be a good design?) > > > > Kind regards > > > > -- > > 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 <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > 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.
