I've used sqlalchemy for many years and something that has come up now and
then is the need for adding a relationship to a mapper that normally would
be a collection (uselist=True) but instead we want to target a specific
record in that collection.
As a simplified illustration, suppose you have CreditApp and Applicant
classes mapped.
mapper(CreditApp, creditapp_table,
properties = {
'applicants': relationship(Applicant,
backref='app')
})
That would work fine if you are happy to work with *applicants *as a
collection.
BUT, in this case we really want 2 very specific 1:1 Applicant
relationships, the primary Applicant and a secondary (joint-signer)
Applicant:
We can hack at the primaryjoin:
mapper(CreditApp, creditapp_table,
properties={
'primaryapplicant': relationship(Applicant,
primaryjoin=and_(
creditapp_table.c.id == applicant_table.c.appid,
applicant_table.c.primary == u'Y',
# <====== THIS IS WHAT WE DON'T WANT
),
foreign_keys=[applicant_table.c.appid],
uselist=False,
backref='app'),
'secondaryapplicant': relationship(Applicant,
primaryjoin=and_(
creditapp_table.c.id == applicant_table.c.appid,
applicant_table.c.primary == u'N',
# <====== THIS IS WHAT WE DON'T WANT
),
foreign_keys=[applicant_table.c.appid],
uselist=False,
backref='app'),
})
This kind of works, but it is ugly since sqlalchemy doesn't really
understand what we've done.
For example, if I set
myapp.primaryapplicant = Applicant()
sqlalchemy doesn't really understand the new record should have primary
flag set to 'Y'
Also:
myapp.primaryapplicant = None
may issue SQL that deletes both applicants if I recall.
What is a better recipe for this? Would association proxies help? Would
polymorphic inheritance work this out properly (single table inheritance)?
Please let me know. Thanks!
Kent
--
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].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
