multiple association proxys to same table: retreive filtered list by extra data field

[Jürg Rast]

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

[mike bayer]

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+...@googlegroups.com
> <mailto:sqlalchemy+...@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.
> For more options, visit https://groups.google.com/d/optout.

[Jürg Rast]

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. 

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Jürg Rast]

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?

> <mailto:sqlalchemy+unsubscribe@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.
> 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 sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[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?
>
> 2017-03-18 12:43 GMT+01:00 Jürg Rast <jue...@gmail.com

> <mailto:jue...@gmail.com>>:

>
> 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>)

> <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>)

> <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>)

> > <mailto:sqlalchemy+...@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+...@googlegroups.com
> <mailto:sqlalchemy+...@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 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+...@googlegroups.com>.

[Jürg Rast]

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?
>
>

>         > <mailto:sqlalchemy+unsub...@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+unsubscribe@googlegroups.com
>     <mailto:sqlalchemy+unsub...@googlegroups.com>.

>     To post to this group, send email to sqlal...@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+unsubscribe@googlegroups.com
> <mailto:sqlalchemy+unsub...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlalchemy@googlegroups.com>.