Sqla烧瓶两个外键同表

Question

我有一个表格"ProductVariant">

class ProductVariant(sqla.Model, ExtendMixin, ResourcesMixin):
vendor_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('vendors.id'),
nullable=True)
decorator_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('vendors.id'),
nullable=True)

我不明白我需要如何在"供应商"表中创建一个反向引用

class Vendor(sqla.Model, ExtendMixin):
pv_vendors = sqla.relationship(
'ProductVariant',
backref='vendor')
pv_decorators = sqla.relationship(
'ProductVariant',
backref='vendor')

我有一个错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Vendor.pv_vendors - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

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

Answer 1

您需要指定使用 backref 引用哪个外键，因为它不明确

class ProductVariant(sqla.Model, ExtendMixin, ResourcesMixin):
vendor_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('vendors.id'),
nullable=True)
decorator_id = sqla.Column(
sqla.Integer,
sqla.ForeignKey('vendors.id'),
nullable=True)

class Vendor(sqla.Model, ExtendMixin):
pv_vendors = sqla.relationship(
'ProductVariant',
backref=backref('vendor', foreign_keys="ProductVariant.vendor_id"))
pv_decorators = sqla.relationship(
'ProductVariant',
backref=backref('decorator', foreign_keys="ProductVariant.decorator_id"))