On Thu, Apr 4, 2019 at 9:34 AM Kent wrote:
>
> 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)?
Single table inheritance might work well for this. It would be
adding in that WHERE clause automatically.Took many years for
single inheritance to support most/all cases but POC below works
including with joinedload/selectinload whatever
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
b1 = relationship("B1", uselist=False)
b2 = relationship("B2", uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
__mapper_args__ = {
"polymorphic_on": data
}
class B1(B):
__mapper_args__ = {
"polymorphic_identity": "b1"
}
class B2(B):
__mapper_args__ = {
"polymorphic_identity": "b2"
}
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(b1=B1(), b2=B2()))
s.commit()
a1 = s.query(A).first()
print(a1.b1)
print(a1.b2)
>
> 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.
--
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.
