SQLAlchemy中如何为多外键表指定默认关联路径及模型优化疑问

首先，咱们先明确问题根源：你的Asset表有三个外键指向User表，当你执行join(User, Asset)时，SQLAlchemy没法自动判断该用哪个外键关联，所以抛出了AmbiguousForeignKeysError。你不想修改大量已有的查询代码，希望通过调整模型来解决，同时还纠结created_by和updated_by这类字段是否该在模型中标记为外键——咱们一步步来解决。

一、模型层面解决默认关联的方案 #

你想要的类似do_not_use_for_joins=True的效果，SQLAlchemy虽然没有直接提供这个参数，但可以通过以下两种方式实现：

1. 只给核心关联创建relationship，其他外键仅保留字段 #

如果created_by和updated_by只是用来记录操作人ID，平时很少需要通过它们关联查询User，那可以只给user_id创建对应的relationship，其他两个外键仅定义字段，不创建relationship：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)


class Asset(Base):
    __tablename__ = 'asset'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    # 仅为核心关联创建relationship
    user = relationship(User, foreign_keys=[user_id])

    # 仅定义外键字段，不创建对应的relationship
    created_by = Column(Integer, ForeignKey("user.id"))
    updated_by = Column(Integer, ForeignKey("user.id"))

这样SQLAlchemy在自动join时，只会识别到user_id对应的relationship，不会再出现歧义，你原来的select(User).join(Asset)就能正常运行了。

2. 给所有外键创建relationship，但明确核心关联的默认路径 #

如果需要通过created_by/updated_by关联查询用户，那可以给它们创建viewonly=True的relationship，同时在User模型中定义反向关联，明确默认的join路径：

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    # 定义反向关联，明确使用Asset.user_id作为默认关联键
    assets = relationship("Asset", foreign_keys="[Asset.user_id]", back_populates="user")


class Asset(Base):
    __tablename__ = 'asset'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User, foreign_keys=[user_id], back_populates="assets")

    created_by = Column(Integer, ForeignKey("user.id"))
    # viewonly=True表示这个relationship仅用于查询，不参与写入和自动关联
    created_by_user = relationship(User, foreign_keys=[created_by], viewonly=True)
    updated_by = Column(Integer, ForeignKey("user.id"))
    updated_by_user = relationship(User, foreign_keys=[updated_by], viewonly=True)

此时执行select(User).join(Asset)时，SQLAlchemy会自动使用User.assets这个反向关联的条件（即Asset.user_id == User.id），完美适配你已有的查询代码。

二、关于created_by/updated_by是否该标记为外键的疑问 #

不建议不在模型中标记外键，只在数据库中保留约束，原因有这几点：

• 数据完整性：SQLAlchemy可以通过模型中的外键定义，在代码层面提前拦截非法的操作人ID，避免数据库出现无效关联。
• 迁移便利性：如果用Alembic做数据库迁移，模型中的外键定义可以自动生成数据库外键约束，不用手动写SQL。
• 查询便捷性：即使平时用得少，当需要查询某个资产的创建人时，直接通过asset.created_by_user就能获取，不用手动写join条件。

如果担心这些外键干扰自动join，用上面第一种或第二种方案即可——既保留外键的好处，又不会让它们参与默认的关联逻辑。

备注：内容来源于stack exchange，提问作者julia uss