ForeignKey serialized as empty dict and not getting populated with data

Question

I've two models Profile & Product representing One-Many relationship. One profile can have many products. I'm serializing all the fields. The column which has ForeignKey is coming out to be empty dictionary. The following model will make my issue more clear.

from backend_olx import db
from marshmallow import Schema, fields
from datetime import datetime

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created_by = db.Column(db.Integer, db.ForeignKey('profile.id'), nullable=False)
    purchased_by = db.Column(db.Integer, db.ForeignKey('profile.id'), nullable=True)
    name = db.Column(db.String(50), nullable=False)
    price = db.Column(db.Integer, nullable=False)
    description = db.Column(db.Text, nullable=False)

    def __repr__(self):
        return '<Product Name %r>' % self.name

class Profile(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), nullable=False, unique=True)
    email = db.Column(db.String(100), nullable=False, unique=True)
    products_sold = db.relationship('Product', backref='profile_sold', foreign_keys="Product.created_by",lazy=True)
    products_purchased = db.relationship('Product', backref='profile_purchased', foreign_keys="Product.purchased_by",lazy=True)

    def __repr__(self):
        return '<User %r>' % self.username

class ProfileSchema(Schema):
    id = fields.Int(dump_only=True)
    username = fields.Str()
    email = fields.Str()
    products_sold = fields.Nested('ProductSchema', many=True)
    products_purchased = fields.Nested('ProductSchema', many=True)


class ProductSchema(Schema):
    id = fields.Int(dump_only=True)
    name = fields.Str()
    price = fields.Int()
    created_by = fields.Nested('ProfileSchema')
    purchased_by = fields.Nested('ProfileSchema')



profile_schema = ProfileSchema()
profiles_schema = ProfileSchema(many=True)
product_schema = ProductSchema()
products_schema = ProductSchema(many=True)

The Nested() method in ProfileSchema is working as expected but it is giving { } in ProductSchema. I want created_by and purchased_by fields to be populated as well.

How to go about ths?

Answer 1

You can't just pass a foreign key and expect Nested to know what to do about it. You need to pass a relation instead.

Create a relation for both fields and use the relation name in the schema.

I typically use xxx_id for column name and xxx for relation name.

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created_by_id = db.Column(db.Integer, db.ForeignKey('profile.id'), nullable=False)
    purchased_by_id = db.Column(db.Integer, db.ForeignKey('profile.id'), nullable=True)
    name = db.Column(db.String(50), nullable=False)
    price = db.Column(db.Integer, nullable=False)
    description = db.Column(db.Text, nullable=False)

    # Setup relations here
    created_by = db.relationship(...)
    purchased_by = db.relationship(...)


class ProductSchema(Schema):
    id = fields.Int(dump_only=True)
    name = fields.Str()
    price = fields.Int()
    created_by = fields.Nested('ProfileSchema')
    purchased_by = fields.Nested('ProfileSchema')