flask-admin + sqlalchemy - modify "sets" of related records in parent model view
I am building a product admin with flask-admin. Products can have single or multiple variant sets. For example a t-shirt can have a "size" set, containing records for S, M, L. A jacket can have both "size" and "color" (red, green, yellow, etc) sets.
Ideally I'd like the administrators to be able to CRUD all product variants from the product view. All other fields on the product view would just be the regular flask-admin default fields for the model. Example sketch:
I'm new to flask-admin, so before I start hackjobbing this up, is there a previously-trodden path for building out such relationships in the a flask-admin-driven UI? I'm thinking some field override that loads a custom widget (or another model view) either in-page or in a modal window?
A simplified outline of the approach I'm taking with flask-sqlalchemy is below.
products
-----------------------------
| skuid | name |
-----------------------------
| B1234 | Test Product 1 |
-----------------------------
| B1235 | Test Product 2 |
-----------------------------
class Product(db.Model):
__tablename__ = 'products'
skuid = db.Column(db.String(16), primary_key=True)
name = db.Column(db.String(128))
variants = db.relationship("Option", secondary="products_to_options")
products_to_options
------------------------
| skuid | variant_set |
------------------------
| B1234 | B1234_1 |
------------------------
| B1234 | B1234_2 |
------------------------
| B1235 | B1235_1 |
------------------------
class ProductToOption(db.Model):
__tablename__ = 'products_to_options'
skuid = db.Column(db.String(16), db.ForeignKey('products.skuid'), nullable=False)
variant_set = db.Column(db.String(16), db.ForeignKey('options.variant_set'), nullable=False)
products = db.relationship('Product', foreign_keys="ProductToOption.skuid")
variants = db.relationship('Option', foreign_keys="ProductToOption.variant_set")
options
-----------------------------------------------------
| variant_set | code | variant_type | description |
-----------------------------------------------------
| B1234_1 | S | size | Small |
-----------------------------------------------------
| B1234_1 | M | size | Medium |
-----------------------------------------------------
| B1234_1 | L | size | Large |
-----------------------------------------------------
| B1234_2 | RD | color | Red |
-----------------------------------------------------
| B1234_2 | GR | color | Green |
-----------------------------------------------------
| B1234_2 | YL | color | Yellow |
-----------------------------------------------------
| B1235_1 | OK | wood | Oak |
-----------------------------------------------------
| B1235_1 | CH | wood | Cherry |
-----------------------------------------------------
class Option(db.Model):
__tablename__ = 'options'
variant_set = db.Column(db.String(16), nullable=False)
code = db.Column(db.String(8), nullable=False)
variant_type = db.Column(db.String(16), db.ForeignKey('option_types.id'), nullable=False)
description = db.Column(db.String(16), nullable=False)
product = db.relationship("Product", secondary="products_to_options")
type = db.relationship("OptionType", foreign_keys="Option.variant_type")
option_types
------------------------
| id | description |
------------------------
| size | Garment Size |
------------------------
| color | Garment Color|
------------------------
| wood | Wood Type |
------------------------
| ring | Ring Size |
------------------------
| shoe | Shoe Size |
------------------------
class OptionType(db.Model):
__tablename__ = 'option_types'
id = db.Column(db.String(16), primary_key=True)
description = db.Column(db.String(36), nullable=False)
It would appear that you are actually trying something in the line of parent-child-child construction here, where the first child is the set. For instance Product has child Garment Size and its child table holds the Small, Medium and Large sizes. So if you change your db model to this idea, that part could work.
Downside is that, as far as I know, flask-admin cannot (yet) provide a create tab that supports 3 tables (parent, child and its child). That would have to be something you need to build yourself.