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