CODEWITHSUNDEEP
pythonflasksqlalchemymany-to-manyflask-sqlalchemy
kontur
kontur

Reputation: 5220

Many-to-many join table with additional field in Flask

I have two tables, Products and Orders, inside my Flask-SqlAlchemy setup, and they are linked so an order can have several products:

class Products(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ....

class Orders(db.Model):
    guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
    products = db.relationship(
        "Products", secondary=order_products_table, backref="orders")
    ....

linked via:

order_products_table = db.Table("order_products_table",
    db.Column('orders_guid', db.String(36), db.ForeignKey('orders.guid')),
    db.Column('products_id', db.Integer, db.ForeignKey('products.id'))
    # db.Column('license', dbString(36))
)

For my purposes, each product in an order will receive a unique license string, which logically should be added to the order_products_table rows of each product in an order.

How do I declare this third license column on the join table order_products_table so it gets populated it as I insert an Order?

Upvotes: 0

Views: 746

Answers (1)

kontur
kontur

Reputation: 5220

I've since found the documentation for the Association Object from the SQLAlchemy docs, which allows for exactly this expansion to the join table.

Updated setup:

# Instead of a table, provide a model for the JOIN table with additional fields 
# and explicit keys and back_populates:
class OrderProducts(db.Model):
    __tablename__ = 'order_products_table'
    orders_guid = db.Column(db.String(36), db.ForeignKey(
        'orders.guid'), primary_key=True)
    products_id = db.Column(db.Integer, db.ForeignKey(
        'products.id'), primary_key=True)

    order = db.relationship("Orders", back_populates="products")
    products = db.relationship("Products", back_populates="order")

    licenses = db.Column(db.String(36), nullable=False)

class Products(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order = db.relationship(OrderProducts, back_populates="order")
    ....

class Orders(db.Model):
    guid = db.Column(db.String(36), default=generate_uuid, primary_key=True)
    products = db.relationship(OrderProducts, back_populates="products")
    ....

What is really tricky (but also shown on the documentation page), is how you insert the data. In my case it goes something like this:

o = Orders(...) # insert other data
for id in products:
    # Create OrderProducts join rows with the extra data, e.g. licenses
    join = OrderProducts(licenses="Foo")
    # To the JOIN add the products 
    join.products = Products.query.get(id)
    # Add the populated JOIN as the Order products
    o.products.append(join)

# Finally commit to database
db.session.add(o)
db.session.commit()

I was at first trying to populate the Order.products (or o.products in the example code) directly, which will give you an error about using a Products class when it expects a OrderProducts class.

I also struggled with the whole field naming and referencing of the back_populates. Again, the example above and on the docs show this. Note the pluralization is entirely to do with how you want your fields named.

Upvotes: 2

Related Questions