Modeling Relationships In Sqlalchemy To Show Nested "sets" With Marshmallow

I'm trying to figure out how to model (flask-)SQLAlchemy and (flask-)Marshamallow to provide the following JSON output. The parent is a product and the children are 'variant sets

Solution 1:

Think I figured this out. I modified the SQLAlchemy relationships between the tables and removed the "secondary" attribute. The relationship is now:

skuid -> variant_set -> variants

And that seems to work. Or at least, I'm getting my desired output.

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

    skuid = db.Column(db.String(16), primary_key=True)
    name = db.Column(db.String(128))
    variant_sets = db.relationship("ProductToOption")

class ProductToOption(db.Model):
    __tablename__ = 'products_to_options'

    id = db.Column(db.INTEGER, primary_key=True)
    skuid = db.Column(db.String(36), db.ForeignKey('products.skuid'), nullable=False)
    options_code = db.Column(db.String(36), nullable=False)

    variants = db.relationship('Option')

class Option(db.Model):
    __tablename__ = 'options'

    variant_set = db.Column(db.String(16), db.ForeignKey('products_to_options.options_code'), nullable=False)
    code = db.Column(db.String(8), nullable=False)
    variant_type = db.Column(db.String(16), nullable=False)
    description = db.Column(db.String(16), nullable=False)