SQLAlchemy多表关联查询生成SQL有误，请求错误排查

Got it, let's break down what's going wrong here and fix it step by step:

1. Your Alias Definitions Are Mixed Up & Incorrect #

Right now, you're creating aliases pointing to Distributor relationship properties instead of the underlying Address and Country tables. You also swapped the commercial/invoice address aliases, which adds to the confusion.

Instead, create aliases directly for the Address and Country tables:

# Create separate aliases for the two Address records we'll join
alias_commercial_address = aliased(Address)
alias_invoice_address = aliased(Address)

# Create aliases for the Country records linked to each Address alias
alias_commercial_address_country = aliased(Country)
alias_invoice_address_country = aliased(Country)

2. You're Joining to the Wrong Country Relationship #

When joining the country tables, you used Address.country (the original table's relationship) instead of linking to the address alias's country property. This is why SQLAlchemy kept reusing the first address table (addresses_1) for both country joins.

3. Corrected Full Query Code #

Here's the fixed query that will generate the correct SQL JOINs:

alias_commercial_address = aliased(Address)
alias_invoice_address = aliased(Address)
alias_commercial_address_country = aliased(Country)
alias_invoice_address_country = aliased(Country)

return Distributor.query\
    .outerjoin(alias_commercial_address, Distributor.commercial_address)\
    # Join the country to the COMMERCIAL address alias, not the base Address table
    .outerjoin(alias_commercial_address_country, alias_commercial_address.country)\
    .outerjoin(alias_invoice_address, Distributor.invoice_address)\
    # Join the country to the INVOICE address alias, not the base Address table
    .outerjoin(alias_invoice_address_country, alias_invoice_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()

Why This Works #

• By creating aliases for the base Address and Country tables, we give SQLAlchemy clear targets for each join.
• Linking each country alias to its corresponding address alias ensures the final JOIN condition uses addresses_2.country_id for the invoice address's country, exactly what you need.

内容的提问来源于stack exchange，提问作者Juan Manuel