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]
<mailto:[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/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 [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
<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
[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
<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 [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.
