使用SQLAlchemy的多个连接

Question

我有这个数据库模型。。。在

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)

…然后我对数据库进行查询。。。在

^{pr2}$

…但是框架生成的查询不正确。。。在

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

…最后一个连接应该是。。。在

在countries\u 2.id=地址_2.country_id上，将外部加入国家作为国家2

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

谢谢！在

Answer 1

根据您创建的别名进一步联接：

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()