在SQLAlchemy中建模关系以显示嵌套的“集合".用棉花糖 [英] Modeling relationships in SQLAlchemy to show nested "sets" with Marshmallow

问题描述

我试图弄清楚如何对(flask-)SQLAlchemy和(flask-)Marshamallow进行建模，以提供以下JSON输出.

I'm trying to figure out how to model (flask-)SQLAlchemy and (flask-)Marshamallow to provide the following JSON output.

父级是产品，子级是变量集" ...，它们是选项的类型，例如颜色"或大小".嵌套在这些集合中，我需要选项本身(S，M，L等)

The parent is a product and the children are "variant sets"... which are types of options, like "color" or "size". Nested within those sets I want the options themselves (S, M, L, etc.)

好像我缺少明显的东西.

Seems like I'm missing something obvious.

所需的输出:

{
  "skuid": "B1234",
  "name": "Test Product 1",
  "variant_sets": [
    {
        "variant_set": "B1234_1",
        "variants": [
            {
                "code": "S",
                "variant_type": "size",
                "description": "Small
            }, 
            {
                "code": "M",
                "variant_type": "size",
                "description": "Medium
            },
            {
                "code": "L",
                "variant_type": "size",
                "description": "Large
            }
        ]
    },
    {
        "variant_set": "B1234_2",
        "variants": [
            {
                "code": "RD",
                "variant_type": "color",
                "description": "Small
            }, 
            {
                "code": "GR",
                "variant_type": "color",
                "description": "Green
            },
            {
                "code": "YL",
                "variant_type": "color",
                "description": "Yellow
            }
        ]
    }
  ]
}

到目前为止我所拥有的棉花糖模式:

The marshmallow schemas I have thus far:

class ProductToOptionSchema(ma.ModelSchema):
    variants = ma.Nested(OptionSchema, many=True)

    class Meta:
        model = ProductToOption


class ProductSchema(ma.ModelSchema):
    variant_sets = ma.Nested(ProductToOptionSchema, many=True)

    class Meta:
        model = Product

当我尝试此代码时:

product = Product.query.filter_by(skuid="B1234").first()
product_schema = ProductSchema()
result = product_schema.jsonify(product)

我得到的错误是:

TypeError: 'Option' object is not iterable

产品通过辅助表与变量(选项)相关.到目前为止，我拥有的模型是:

The products are related to variants (options) through a secondary table. The models I have so far are:

产品

-----------------------------
| 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

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")

选项

-----------------------------------------------------
| 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), nullable=False)
    description = db.Column(db.String(16), nullable=False)

    product = db.relationship("Product", secondary="products_to_options")

推荐答案

我想出了这一点.我修改了表之间的SQLAlchemy关系，并删除了"secondary"属性.现在的关系是:

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->变体

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)

这篇关于在SQLAlchemy中建模关系以显示嵌套的“集合".用棉花糖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！