Ok, I think the option with two assiociation tables is more sane. Thank you for the advices.
Am Samstag, 18. März 2017 20:07:34 UTC+1 schrieb Mike Bayer: > > if you want an "easier" solution, it's just use two different > association tables, one for "participants" and one for "guides". Then > make two relationships using a different argument for "secondary". The > association object pattern is usually about some kind of information > that needs to travel along with the association (like an amount paid, or > a date, or something). but in this case it's a kind of discriminator. > using two separate tables / relationships is the easy way to get that. > > On 03/18/2017 08:11 AM, Jürg Rast wrote: > > 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? > > > > > > 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 > > < > 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 > > <http://event.id>'), primary_key=True) > > > member_id = Column(Integer, ForeignKey('member.id > > <http://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 <http://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 > > <http://event.id>'), primary_key=True) > > member_id = Column(Integer, ForeignKey('member.id > > <http://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 <http://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 > > <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 > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > > > To post to this group, send email to sqlal...@googlegroups.com > > > <mailto:sqlal...@googlegroups.com>. > > > Visit this group at https://groups.google.com/group/sqlalchemy > > <https://groups.google.com/group/sqlalchemy>. > > > For more options, visit https://groups.google.com/d/optout > > <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 > > <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 > > < > https://groups.google.com/d/topic/sqlalchemy/nUNerrokPVE/unsubscribe>. > > To unsubscribe from this group and all its topics, send an email to > > sqlalchemy+unsubscr...@googlegroups.com > > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > > To post to this group, send email to sqlalchemy@googlegroups.com > > <mailto:sqlalchemy@googlegroups.com>. > > Visit this group at https://groups.google.com/group/sqlalchemy > > <https://groups.google.com/group/sqlalchemy>. > > For more options, visit https://groups.google.com/d/optout > > <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 > > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > > To post to this group, send email to sqlalchemy@googlegroups.com > > <mailto: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.
