使用SQLAlchemy的多个联接 [英] Multiple joins with SQLAlchemy

问题描述

我有这个数据库模型...

I have this database model...

class Distributor(Base):  
    __tablename__ = 'distributor'
    id = Column(Integer, primary_key=True)
    commercial_address_id = Column(Integer, ForeignKey('addresses.id'), nullable=False, unique=True)
    invoice_address_id = Column(Integer, ForeignKey('addresses.id'), nullable=False, unique=True)
    commercial_address = relationship(Address, lazy="noload", foreign_keys=[commercial_address_id])
    invoice_address = relationship(Address, lazy="noload", foreign_keys=[invoice_address_id])

class Address(Base):  
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    address_name = Column(String(64))
    country_id = Column(Integer, ForeignKey('countries.id'), nullable=False)
    country = relationship(Country, lazy="noload")

class Country(Base):  
    __tablename__ = 'country'
    id = Column(Integer, primary_key=True)
    code = Column(Integer)

...然后我对数据库进行查询...

...then I do the query to the database...

alias_commercial_address = aliased(Distributor.invoice_address)
alias_commercial_address_country = aliased(Address.country)
alias_invoice_address = aliased(Distributor.commercial_address)
alias_invoice_address_country = aliased(Address.country)

return Distributor.query\
    .outerjoin(alias_commercial_address, Distributor.commercial_address)\
    .outerjoin(alias_commercial_address_country, Address.country)\
    .outerjoin(alias_invoice_address, Distributor.invoice_address)\
    .outerjoin(alias_invoice_address_country, Address.country)\
    .options(contains_eager(Distributor.commercial_address, alias=alias_commercial_address)
             .contains_eager(Address.country, alias=alias_commercial_address_country),
             contains_eager(Distributor.invoice_address, alias=alias_invoice_address)
             .contains_eager(Address.country, alias=alias_invoice_address_country))\
    .all()

...但是框架错误地生成了查询...

...but the framework generates the query incorrectly...

SELECT ... FROM distributors
LEFT OUTER JOIN addresses AS addresses_1 ON addresses_1.id = distributors.commercial_address_id
LEFT OUTER JOIN countries AS countries_1 ON countries_1.id = addresses_1.country_id 
LEFT OUTER JOIN addresses AS addresses_2 ON addresses_2.id = distributors.invoice_address_id 
LEFT OUTER JOIN countries AS countries_2 ON countries_2.id = addresses_1.country_id

...最后一个JOIN应该是...

...the last JOIN should be...

向左加入其他国家/地区，作为国家_2到国家_2.id =地址_2.country_id

我尝试了不同的方法来创建SQL查询，但是我无法正确生成查询.知道我的错误在哪里吗?

I have tried different ways to create the SQL query, but I can not get to generate the query correctly. Any idea where is my error?

谢谢！

推荐答案

进一步的联接基于您创建的别名:

Base your further joins on the aliases you've created:

Distributor.query\
    .outerjoin(alias_commercial_address, Distributor.commercial_address)\
    .outerjoin(alias_commercial_address_country, alias_commercial_address.country)\
    .outerjoin(alias_invoice_address, Distributor.invoice_address)\
    .outerjoin(alias_invoice_address_country, alias_invoice_address.country)\
    .options(contains_eager(Distributor.commercial_address, alias=alias_commercial_address)
             .contains_eager(alias_commercial_address.country, alias=alias_commercial_address_country),
             contains_eager(Distributor.invoice_address, alias=alias_invoice_address)
             .contains_eager(alias_invoice_address.country, alias=alias_invoice_address_country))\
    .all()

这篇关于使用SQLAlchemy的多个联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！