Marshmallow-Flask生成嵌套JSON响应时商品分类信息缺失问题

我尝试使用Marshmallow为OrderPlaced、Product和Category对象生成嵌套JSON格式的API响应。目前订单关联的用户与商品信息可正常展示，但商品所属的分类信息无法正确显示，返回的category_id是个空对象。

实现代码 #

models.py

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    parent_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)
    total_product = db.Column(db.Integer, nullable=True, default=0)
    category_id = db.relationship('Category', backref="parentcategory", remote_side=[id])
    product = db.relationship('Product', backref='category')

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(100), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=True)
    order_detail = db.relationship('OrderPlaced', backref='product')
    
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100))
    email = db.Column(db.String(100))
    file = db.Column(db.String(20))
    filedata = db.LargeBinary()
    order_detail = db.relationship('OrderPlaced', backref="user")

    
class OrderPlaced(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    product_id = db.Column(db.Integer(), db.ForeignKey('product.id'))
    quantity = db.Column(db.Integer)
    order_status = db.Column(db.String(10))
    created_at = db.Column(db.DateTime, default=datetime.datetime.now())
    modified_at = db.Column(db.DateTime, default=datetime.datetime.now())

orders.py

class UserSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = User

class CategorySchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Category
        

class ProductSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        fields = ('name', 'category_id')
        model = Product
    category_id = ma.Nested(CategorySchema)

class OrderSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = OrderPlaced
    
    user = ma.Nested(UserSchema)
    product = ma.Nested(ProductSchema)

api.add_resource(OrderDetailList, '/order/<int:user_id>')
class OrderDetailList(Resource):
    def get(self, user_id):
        user = User.query.filter_by(id=user_id).first()

        if not user:
            abort(404, message="User doesnt exist")
        
        user_orders = OrderPlaced.query.filter_by(user_id=user.id).all()
        if len(user_orders) == 0:
            abort(404, message="Currently no orders placed")
        else:
            return orders_schema.dump(user_orders)

当前响应 #

[
    {
        "id": 1,
        "created_at": "2023-02-08T15:31:50.103204",
        "order_status": "pending",
        "user": {
            "id": 1,
            "file": "file.pdf",
            "name": "john",
            "email": "blabla@gmail.com1"
        },
        "modified_at": "2023-02-08T15:31:50.103310",
        "quantity": 1,
        "product": {
            "category_id": {},
            "name": "Iphone 14"
        }
    }
]

期望响应 #

[
    {
        "id": 1,
        "created_at": "2023-02-08T15:31:50.103204",
        "order_status": "pending",
        "user": {
            "id": 1,
            "file": "file.pdf",
            "name": "john",
            "email": "blabla@gmail.com1"
        },
        "modified_at": "2023-02-08T15:31:50.103310",
        "quantity": 1,
        "product": {
            "category": {
                "id": 3,
                "name": "Apple"
             },
            "name": "Iphone 14"
        }
    }
]

核心错误点 #

1. Schema字段关联错误：在ProductSchema中，你试图嵌套category_id字段，但category_id只是数据库中存储的整数外键，而SQLAlchemy通过backref='category'为Product模型创建了关联属性category，这个属性才是指向对应Category实例的对象。把嵌套绑定到整数字段上，自然无法序列化出分类信息。
2. 未预加载关联数据：查询订单时没有预加载商品的分类数据，SQLAlchemy默认的懒加载可能在序列化时无法获取到关联对象，导致返回空结构。

修复步骤 #

1. 修改ProductSchema #

将嵌套字段从category_id改为category，并调整fields配置：

class ProductSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        # 改为包含name和category字段
        fields = ('name', 'category')
        model = Product
    # 嵌套关联的category属性
    category = ma.Nested(CategorySchema)

如果需要同时保留category_id（整数）和分类对象，可以把fields改成('name', 'category_id', 'category')，但通常嵌套对象已经包含id，无需重复返回。

2. 优化查询，预加载关联数据 #

在查询订单时，使用joinedload或selectinload预加载商品及分类，避免N+1查询并确保数据可被序列化：

from sqlalchemy.orm import joinedload

class OrderDetailList(Resource):
    def get(self, user_id):
        user = User.query.filter_by(id=user_id).first()

        if not user:
            abort(404, message="User doesnt exist")
        
        # 预加载product和product的category
        user_orders = OrderPlaced.query.filter_by(user_id=user.id)\
            .options(joinedload(OrderPlaced.product).joinedload(Product.category))\
            .all()
        if len(user_orders) == 0:
            abort(404, message="Currently no orders placed")
        else:
            return orders_schema.dump(user_orders)

3. 确认orders_schema的定义 #

确保你已经正确初始化了多实例的Schema：

# 在orders.py中添加
orders_schema = OrderSchema(many=True)

完成以上修改后，序列化后的响应就能正确展示商品所属的分类信息了。

内容的提问来源于stack exchange，提问作者Ankur Paranjpe