CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
tarponjargon
tarponjargon

Reputation: 1032

SQLAlchemy: modeling same-table relationship via secondary table

I have a products table and I want to create relationships between products (i.e. "you may also like these products...")

There's a secondary table called product_related that contains the data. How do I model this in SQLAlchemy?

products

-----------------------------
| skuid | name              |
-----------------------------
| B1234 | Test Product 1    |
-----------------------------
| B1235 | Test Product 2    |
-----------------------------
| B1236 | Test Product 3    |
-----------------------------

product_related

--------------------
| skuid | related  |
--------------------
| B1234 | B1235    |
--------------------
| B1234 | B1236    |
--------------------
| B1235 | B1234    |
--------------------
| B1235 | B1236    |
--------------------
| B1236 | B1234    |
--------------------
| B1236 | B1235    |
--------------------

My attempts (using flask-sqlalchemy) are:

class Product(db.Model):
    __tablename__ = 'products'

    skuid = db.Column(db.String(16), primary_key=True)
    name = db.Column(db.String(128))
    related = db.relationship("Product", secondary="product_related")

class ProductToRelated(db.Model):
    __tablename__ = 'product_related'

    skuid = db.Column(db.String(36), db.ForeignKey('products.skuid'), nullable=False)
    related_skuid = db.Column(db.String(36), db.ForeignKey('products.skuid'), nullable=False)

    product = db.relationship('Product', foreign_keys="ProductToRelated.skuid")
    related_product = db.relationship('Product', foreign_keys="ProductToRelated.related_skuid")

The error I get is:

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

Upvotes: 0

Views: 350

Answers (1)

EAW
EAW

Reputation: 952

The reason for the error is that there are two foreign keys to Products in ProductToRelated and therefore the relationship cannot work out which one to use unless it is explicitly stated. The following should work.

product = db.relationship("Product", primaryjoin="ProductToRelated.skuid=Product.skuid")
related_product = db.relationship("Product", primaryjoin="ProductToRelated.related_skuid=Product.skuid")

What you are creating here is a self referential many to many relationship between Products and itself using ProductToRelated as an association table. It may be better to model this as described here https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#self-referential-many-to-many-relationship in the sqlalchemy docs rather than using two separate mapped classes to describe the same object.

Upvotes: 1

Related Questions