Relationship configuration that isn't functioning as I expect

[Bobby Impollonia]

I am trying to create a relationship to work with a legacy schema and am having trouble configuring it to behave as I want. As a disclaimer, I understand why the schema here is not the schema one would use if starting from scratch.

Anyway, I have simplied the situation down to the following example:

I have users and have two different types of objects that users can be related to. For this example, those are street addresses and email addresses. Rather than having a secondary table for each relationship, there is a single secondary table. It has a "foreign key" column that will either function as a foreign key to the street addresses table or the email addresses table. There is a discriminator column that indicates which of those tables is being referenced. The secondary table has its own primary key and has an associated mapped class. The problem I am trying to solve is setting up a relationship between the secondary table and the address tables.

This is what I have ended up with:

class Affiliation(Base):

    __tablename__ = 'affiliations'

    id = Column(Integer, primary_key=True)

    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)

    address_type = Column(String, nullable=False)

    address_id = Column(Integer, nullable=False)

    user = relationship("User", backref='affiliations')

    email_address = relationship(

        'EmailAddress',

        primaryjoin=and_(address_type == 'email',

                         address_id  == EmailAddress.id),

        foreign_keys=[address_id],

        viewonly=True)

    street_address = relationship(

        'StreetAddress',

        primaryjoin=and_(address_type == 'street',

                         address_id  == StreetAddress.id),

        foreign_keys=[address_id],

        viewonly=True)

The users, email_addresses and street_addresses tables/ classes are trivial so I am omitting them from this post, but I have a full working example here:

https://gist.github.com/bobbyi/5593984

For a given affiliation, I want affiliation.email_address to be the appropriate email address if this affiliation's type is 'email'. Otherwise, it should be None.

Given the following:

affiliation = session.query(Affiliation).get(1)

print affiliation.email_address

The SQL generated by the second line is:

SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS email_addresses_address

FROM email_addresses, affiliations

WHERE affiliations.address_type = 'email' AND 1 = email_addresses.id

Note that this is a cartesian join.

I hoped to end up with something like:

SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS email_addresses_address

FROM email_addresses, affiliations

WHERE affiliations.address_type = 'email' AND affiliations.address_id = email_addresses.id AND affiliations.id = 1

Two questions:

1) Is the behavior I am seeing here expected given my relationship configuration?

2) How can I configure my relationship to exhibit the desired behavior in the situation shown above?

[Michael Bayer]

On May 16, 2013, at 3:02 PM, Bobby Impollonia <bob...@gmail.com> wrote:

>
> I hoped to end up with something like:
> SELECT email_addresses.id AS email_addresses_id, email_addresses.address AS email_addresses_address
> FROM email_addresses, affiliations
> WHERE affiliations.address_type = 'email' AND affiliations.address_id = email_addresses.id AND affiliations.id = 1
>
> Two questions:
> 1) Is the behavior I am seeing here expected given my relationship configuration?
> 2) How can I configure my relationship to exhibit the desired behavior in the situation shown above?

Ok well lets think of it in terms of joins. What if you wanted to load all the affiliations and EmailAddresses together? the join would be:

select * from affiliations JOIN email_addresses ON affiliations.address_id=email_addresses.id AND affiliations.address_type='email'

above, there's not really a space for "AND affiliations.id = ...something ?", unless maybe if it were equated to itself.

So maybe, this kind of thing would be possible if you could say:

email_address = relationship(
'EmailAddress',
primaryjoin=and_(address_type == 'email',

id == lazy(id),

address_id == EmailAddress.id),
foreign_keys=[address_id],
viewonly=True)

the lazy load would need to figure out to set up a bind for one of the slot there (that's the hypothetical "lazy()" annotation). its funny we're a lot closer to that sort of thing, since we do have situations where we have things like "column == remote(column)" now, but not quite in that arrangement.

But I don't think we need to get into any of that here since your class has a discriminator anyway, we can just use inheritance so that your different Affiliation objects know what to do, see below.


#!/usr/bin/env python
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref, contains_eager, remote

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)


class StreetAddress(Base):
__tablename__ = 'steet_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)


class EmailAddress(Base):
__tablename__ = 'email_addresses'
id = Column(Integer, primary_key=True)
address = Column(String, nullable=False)

class Affiliation(Base):
__tablename__ = 'affiliations'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable = False)
address_type = Column(String, nullable=False)
address_id = Column(Integer, nullable=False)

user = relationship("User", backref='affiliations')

street_address = None
email_address = None

__mapper_args__ = dict(polymorphic_on=address_type)

class EmailAffiliation(Affiliation):

email_address = relationship(
'EmailAddress',

primaryjoin=Affiliation.address_id == EmailAddress.id,
foreign_keys=[Affiliation.address_id],
viewonly=True)
__mapper_args__ = dict(polymorphic_identity='email')

class StreetAffiliation(Affiliation):

street_address = relationship(
'StreetAddress',

primaryjoin=Affiliation.address_id == StreetAddress.id,
foreign_keys=[Affiliation.address_id],
viewonly=True)
__mapper_args__ = dict(polymorphic_identity='street')


Base.metadata.create_all(engine)

user = User(id=1)
email = EmailAddress(id=1, address='y...@localhost.com')
street = StreetAddress(id=1, address='123 Main str.')
session.add_all([
user,
email,
street,
])
session.flush()
street_affiliation = StreetAffiliation(user=user, address_id=1)
email_affiliation = EmailAffiliation(user=user, address_id=1)
session.add_all([
street_affiliation,
email_affiliation,
])
session.flush()
session.commit()


assert street_affiliation.street_address is not None
assert email_affiliation.email_address is not None
assert street_affiliation.email_address is None
assert email_affiliation.street_address is None

[Bobby Impollonia]

That makes sense. I am seeing one problem with this approach and it causes the asserts in original gist to still fail. The problem is that the identity map remembers that I constructed my objects as instances of the parent class even across commit and expire boundaries. So the following assert fails:

street_affiliation = Affiliation(user=user, address_type='street', address_id=1)

session.add(street_affiliation)

session.flush()

session.commit()

street_affiliation = session.query(Affiliation).filter_by(address_type='street').one()

assert isinstance(street_affiliation, StreetAffiliation)

And as a result the asserts in the original gist still fail.

I understand why this happens (the identity map uses weakrefs but there is a still a reference to the old object of type Affiliation) and how to work around it (delete the reference to the old affiliation before querying or construct the affiliation object as a StreetAffiliation instead of as an Affiliation). It is still somewhat surprising since I am used to assuming that once a commit/ expire_all has happened, fetching from the database will give the "right" results, but in this case the affiliation I fetch after the flush incorrectly tells me that its street_address is None.

However, I don't see this being a problem in production and I think this will work for our application. Thanks.

[Michael Bayer]

On May 16, 2013, at 9:17 PM, Bobby Impollonia <bob...@gmail.com> wrote:

That makes sense. I am seeing one problem with this approach and it causes the asserts in original gist to still fail. The problem is that the identity map remembers that I constructed my objects as instances of the parent class even across commit and expire boundaries. So the following assert fails:

street_affiliation = Affiliation(user=user, address_type='street', address_id=1)

right well you don't do that anymore ;)  make it StreetAffiliation.  Or use a factory method like Affiliation.create(type="street"), or add a special __new__ to Affiliation to do the same thing.

I understand why this happens (the identity map uses weakrefs but there is a still a reference to the old object of type Affiliation) and how to work around it (delete the reference to the old affiliation before querying or construct the affiliation object as a StreetAffiliation instead of as an Affiliation). It is still somewhat surprising since I am used to assuming that once a commit/ expire_all has happened, fetching from the database will give the "right" results, but in this case the affiliation I fetch after the flush incorrectly tells me that its street_address is None.

the expire removes the contents of __dict__ of all the objects, but not the objects themselves, which means their type is also preserved in the session.   The objects will fall out of the session if you lose all references to them on the outside, but that is also assuming they are garbage collected which won't necessarily be immediate if there are any cycles.