Just realised, there are more functions which need to be overwritten to get a completly working design. Functions such as __getslice__, __len__, __bool__, and many more need also a new implementation.
It seems as the _AssociationCollection.col is just a getter for the lazy_collection. So is it possible to overwrite this getter somhow to automatically return the correct collection of objects? 2017-03-18 12:43 GMT+01:00 Jürg Rast <[email protected]>: > 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/associa >> tionproxy.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 [email protected] >> > <mailto:[email protected]>. >> > To post to this group, send email to [email protected] >> > <mailto:[email protected]>. >> > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/nUNerrokPVE/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
