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.
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+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.
