Flask SQLAlchemy: How to add a column that depends on another table?

Question

I have three tables in my SQLAlchemy database (using Flask SQLAlchemy): Product, Product variation and Order. I want to see in an order, which product and its variation it includes.

It works well with relationships/foreign keys, but the main problem: if I add a product to order, I can still add variation from another product (using Flask-Admin, or just flask shell).

So, main question: how to create connection between tables, so variations can be added only if they're variations of order product? Thanks :)

Another solution: how to add column to Orders table, so it'll get product name from Product table, based on variation id? I tried to use column_property, Post.query.get(variation_id), variation.parent_id, backhref variation.origin_product but without any success :)

My models:

Product (like Samsung Galaxy 7)

class Product(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), index=True)
    brand = db.Column(db.String(120))
    variations = db.relationship('Variation', backref='origin_product', lazy='dynamic')
    orders = db.relationship('Order', backref='product_in_order', lazy='dynamic')

Product variation (like Samsung Galaxy 7 Blue 32GB)

class Variation(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), index=True)
    price = db.Column(db.Integer)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))
    orders = db.relationship('Order', backref='variation_in_order', lazy='dynamic')

Order

class Order(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    variation_id = db.Column(db.Integer, db.ForeignKey('variation.id'))
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))

P.S. product_id = db.Column(db.Integer, db.ForeignKey('variation.product_id')) works in db, I see right product id. Still external tools like Flask-Admin see product_id column as variation object, so no use. Need a way, to connect product object to product_id. Like, connect product ForeignKey, but based on variation_id.

Hailey

Seemingly silly question: do all orders always reference a variation together with a product, or can some orders have NULL variation_id? – Chronologist

You can create product without a variation (want to add later), but can't add variation without product. The main problem is how ("Another solution" looks better now) to get origin_product from variation, but using Product.db.relationship, so Flask-Admin will see connection as product instance :) – Hailey

I'm being dense, but can you then create an order for a product without a variation? That'll affect the way to solve this some. – Chronologist

Sure, I can. Thanks for trying to help me :) – Hailey

Answer 1

One way to prevent unrelated product and variation combinations would be to create a foreign key from order to product and an overlapping composite foreign key from order to variation. In order to be able to reference the combination of variation.id, variation.product_id the product id should be made a part of the primary key as well and id must be given auto incrementing behaviour explicitly:

class Variation(db.Model):

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'),
                           primary_key=True)


class Order(db.Model):

    product_id = db.Column(db.Integer, nullable=False)
    variation_id = db.Column(db.Integer)

    __table_args__ = (
        db.ForeignKeyConstraint([product_id], ['product.id']),
        db.ForeignKeyConstraint([product_id, variation_id],
                                ['variation.product_id', 'variation.id']),
    )

Since a foreign key defaults to MATCH SIMPLE the composite foreign key to variation will allow adding rows where variation id is NULL, but if variation id is given, the combination must reference an existing row. This setup allows using the existing relationships product_in_order and variation_in_order to both Product and Variation respectively instead of the more involved models below, though SQLAlchemy will (rightly) warn about the fact that the relationships have a conflict in that they both set product id. Just use one of them when creating orders:

In [24]: o1 = Order(product_in_order=product)

In [25]: o2 = Order(variation_in_order=variation)

or follow the documentation about resolving the conflict. In this model the product name is always available as

In [31]: o1.product_in_order.name

---

Another option to prevent adding unrelated variations to orders when the product is given is to prevent adding a variation entirely in that case, and vice versa:

class Order(db.Model):
    ...
    variation_id = db.Column(db.Integer, db.ForeignKey('variation.id'))
    product_id = db.Column(db.Integer, db.ForeignKey('product.id'))

    __table_args__ = (
        # Require either a variation or a product
        db.CheckConstraint(
            '(variation_id IS NOT NULL AND product_id IS NULL) OR '
            '(variation_id IS NULL AND product_id IS NOT NULL)'),
    )

Building the relationship to Product is a bit more complex in this model, and requires using a non primary mapper:

product_variation = db.outerjoin(
    Product, db.select([Variation.id,
                        Variation.product_id]).alias('variation'))

ProductVariation = db.mapper(
    Product, product_variation, non_primary=True,
    properties={
        'id': [product_variation.c.product_id,
               product_variation.c.variation_product_id],
        'variation_id': product_variation.c.variation_id
    })

The selectable produced by the join is mapped back to Product, but allows selecting based on Variation.id as well:

Order.product = db.relationship(
    ProductVariation,
    primaryjoin=db.or_(Order.product_id == ProductVariation.c.id,
                       Order.variation_id == ProductVariation.c.variation_id))

This way you can access the product name from an Order instance with

order.product.name

Demo:

In [2]: p1 = Product(name='Product 1')

In [3]: v11 = Variation(product=p1)

In [4]: v12 = Variation(product=p1)

In [5]: p2 = Product(name='Product 2')

In [6]: v21 = Variation(product=p2)

In [9]: session.add_all([p1, p2])

In [10]: session.add_all([v11, v12, v21])

In [11]: session.commit()

In [12]: o1 = Order(product_id=p1.id)

In [13]: o2 = Order(variation_id=v12.id)

In [14]: o3 = Order(variation_id=v11.id)

In [15]: o4 = Order(product_id=p2.id)

In [16]: o5 = Order(variation_id=v21.id)

In [17]: session.add_all([o1, o2, o3, o4, o5])

In [18]: session.commit()

In [25]: [o.product.name for o in session.query(Order).all()]
Out[25]: ['Product 1', 'Product 1', 'Product 1', 'Product 2', 'Product 2']

The LEFT JOIN ensures that products without variations work as well:

In [26]: p3 = Product(name='Product 3')

In [27]: session.add(p3)

In [28]: session.commit()

In [29]: session.add(Order(product_id=p3.id))

In [30]: session.commit()

In [31]: [o.product.name for o in session.query(Order).all()]
Out[31]: ['Product 1', 'Product 1', 'Product 1', 'Product 2', 'Product 2', 'Product 3']

---

On the other hand instead of this rather complex construction you could use the CheckConstraint as described and an ordinary property:

class Order(db.Model):
    ...
    @property
    def product(self):
        if self.product_in_order:
            return self.product_in_order

        else:
            return self.variation_in_order.origin_product

Just note that without eager loading this will fire 2 separate SELECT queries against the database in case of a variation order.

Vanadium

Wow, awesome :) I half-solved it with column_formatters in Flask-Admin, but your solution is much more solid. Thanks! – Hailey

Added another option to enforce the product, variation pair validity using overlapping foreign keys. – Chronologist