python - sqlalchemy 多个外键到同一个表

Question

我有一个看起来像这样的 postgres 数据库:

      Table "public.entities"
    Column     |            Type             |                   Modifiers                    
---------------+-----------------------------+------------------------------------------------
 id            | bigint                      | not null default nextval('guid_seq'::regclass)
 type_id       | smallint                    | not null
 name          | character varying           | 
Indexes:
    "entities_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "entities_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES entities(id)
    "entities_type_id_fkey" FOREIGN KEY (type_id) REFERENCES entity_types(id)
Referenced by:
    TABLE "posts" CONSTRAINT "posts_id_fkey" FOREIGN KEY (id) REFERENCES entities(id)
    TABLE "posts" CONSTRAINT "posts_subject_1_fkey" FOREIGN KEY (subject_1) REFERENCES entities(id)
    TABLE "posts" CONSTRAINT "posts_subject_2_fkey" FOREIGN KEY (subject_2) REFERENCES entities(id)

    Table "public.posts"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 id        | bigint | not null
 poster_id | bigint | 
 subject_1 | bigint | not null 
 subject_2 | bigint | not null 
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "posts_id_fkey" FOREIGN KEY (id) REFERENCES entities(id)
    "posts_poster_id_fkey" FOREIGN KEY (poster_id) REFERENCES users(id)
    "posts_subject_1_fkey" FOREIGN KEY (subject_1) REFERENCES entities(id)
    "posts_subject_2_fkey" FOREIGN KEY (subject_2) REFERENCES entities(id)

我正在尝试弄清楚如何为“帖子”定义 orm 对象以包含所有 3 个外键。注意只有 id 是主键。其他只是帖子和实体之间的关系，不能被 pk。

class PostModel(EntitiesModel):
    __tablename__ = 'posts'

    id = db.Column(db.BigInteger, db.ForeignKey(EntitiesModel.id), primary_key=True, nullable=False)
    poster_id = db.Column(db.BigInteger, db.ForeignKey(UserModel.id), nullable=False)

    subject_1 = db.Column(db.BigInteger, db.ForeignKey(EntitiesModel.id), nullable=False)
    subject_2 = db.Column(db.BigInteger, db.ForeignKey(EntitiesModel.id), nullable=False)

我试过稍微摆弄一下，除了禁用 subject_1 上的外键外，我似乎无法想出不会导致此错误的解决方案:

AmbiguousForeignKeysError: Can't determine join between 'entities' and 'posts'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

有什么想法吗？

Answer 1

目前还不完全清楚究竟是什么导致了问题，因为您省略了最重要的部分——抛出该异常的代码，但是如果将关系属性添加到类 PostModel 会抛出尝试添加 relationship 调用的 foreign_keys 参数如下:

class PostModel(...):
    # ...
    subject1_id = Column(db.Column(db.BigInteger, db.ForeignKey(EntitiesModel.id), nullable=False)
    subject2_id = Column(db.Column(db.BigInteger, db.ForeignKey(EntitiesModel.id), nullable=False)
    subject1 = relationship(EntitiesModel, foreign_keys=subject1_id)
    subject2 = relationship(EntitiesModel, foreign_keys=subject2_id)