sqlalchemy多个外键到同一表 [英] sqlalchemy multiple foreign keys to same table
问题描述
我有一个类似于以下的postgres数据库:
I have a postgres database that looks something like this:
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 是主键。
I'm trying to figure out how to define the orm object for "posts" to include all 3 of the foreign keys. Notice only id is a primary key. The others are just relationships between posts and entities that are not to be pk'd.
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上的外键外,我似乎无法想出一个不会导致此错误的解决方案:
I've tried fiddling with it a bit, and besides disabling the foreign keys on subject_1 I can't seem to come up with a solution that doesn't result in this error:
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.
有什么想法吗?
推荐答案
由于您省略了最重要的部分-代码会抛出该异常,但是如果将关系属性添加到类 PostModel 中,则会导致尝试将 foreign_keys 参数添加到 relationship 调用中,如下所示:
It's not completely clear what exactly is causing the problem since you omitted the most important part -- code that throws that exception but if adding relationship properties to class PostModel throws that try to add foreign_keys parameter to relationship call as the following:
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)
这篇关于sqlalchemy多个外键到同一表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!