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