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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.