[sqlalchemy] Relationship configuration that isn't functioning as I expect
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?
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Relationship configuration that isn't functioning as I expect
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.
On Thursday, May 16, 2013 12:49:17 PM UTC-7, Michael Bayer wrote:
On May 16, 2013, at 3:02 PM, Bobby Impollonia
bob...@gmail.comjavascript:
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(
Re: [sqlalchemy] Relationship configuration that isn't functioning as I expect
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. -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
