SQLAlchemy关联表查询出现重复关联及条件引用异常问题

问题场景 #

模型定义 #

class Service(Base):
    __tablename__ = "services"

    service_id: Mapped[str] = mapped_column(nullable=False)

    __table_args__ = (
        PrimaryKeyConstraint(
            "service_id",
            name="services_pkey",
        ),
    )

class SMP(Base, common_models.PostgresBaseModel):
    __tablename__ = "smp"

    service_id: Mapped[str] = mapped_column(
        ForeignKey(
            "services.service_id",
            ondelete="CASCADE",
        ),
    )
    service: Mapped["Service"] = relationship(lazy="joined", innerjoin=True)

查询语句 #

stmt = stmt.where(
    Service.service_id == "672f12736ea63f38f35fd9dd",
)
stmt = stmt.options(
    joinedload(SMP.service, innerjoin=True),
)

生成的SQL #

SELECT smps.abc, services_1.xyz, ...
FROM smps 
JOIN services AS services_1 
ON services_1.service_id = smps.service_id, 
services -- 为何会有这张表？？？
WHERE services.service_id = $1::VARCHAR // <-- 为何不使用关联的表？

用户疑问：SQLAlchemy似乎不知道Service.service_id ==应该引用已关联的表，导致额外生成了services表，且WHERE条件未使用joinedload创建的services_1表。

原因分析 #

你直接在WHERE条件中引用Service.service_id，SQLAlchemy会将其视为对Service表的独立查询需求，因此会额外添加一个未关联的services表（笛卡尔积形式），而不会自动关联到joinedload创建的services_1别名表。

joinedload是ORM层面用于预加载关联对象的选项，它创建的表别名仅用于加载关联数据，并不会自动被WHERE条件中的模型字段引用，除非你明确指定使用该别名对应的关联。

解决方法 #

方法一：使用with_loader_criteria针对关联对象添加条件 #

这个方法会让SQLAlchemy自动使用joinedload创建的表别名来过滤：

from sqlalchemy.orm import with_loader_criteria

stmt = stmt.options(
    joinedload(SMP.service, innerjoin=True),
    with_loader_criteria(Service, Service.service_id == "672f12736ea63f38f35fd9dd")
)

方法二：通过SMP的关联或外键字段直接过滤 #

如果你的查询是从SMP表出发，直接利用SMP自身的外键字段或关联关系过滤，更高效且不会产生额外表：

# 利用关联关系的has方法
stmt = stmt.where(SMP.service.has(Service.service_id == "672f12736ea63f38f35fd9dd"))

# 直接使用SMP的外键字段（推荐，无需额外关联）
stmt = stmt.where(SMP.service_id == "672f12736ea63f38f35fd9dd")

内容的提问来源于stack exchange，提问作者Jiew Meng