Flask Marshmallow serialize many to many relation with extra field

Question

I have an issue in Flask application with serialization model object that has a many to many relationship with extra field stored in association table. I would like to have a serialized data looking like so:

{
    "id": "123",
    "name": "name",
    "mobile": "phone number",
    "interest": [1, 2, 3]
    "_embedded": {
        "interest": [
            {
                "id": 1,
                "name": "ECONOMIC",
                "active": true,
            },
            {
                "id": 2,
                "name": "POETRY",
                "active": true,
            },
            {
                "id": 3,
                "name": "SPORT",
                "active": false,
            },
        ]
    }
}

For now I managed to prepare a neccessary models as below:

class OwnerInterests(db.Model):
    owner_id = db.Column(db.Integer, db.ForeignKey('owners.id'), primary_key=True)
    interest_id = db.Column(db.Integer, db.ForeignKey('interests.id'), primary_key=True)
    active = db.Column(db.Boolean)
    interest = db.relationship('Interests', back_populates='owners')
    owner = db.relationship('Owners', back_populates='interests')


class Owners(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    mobile = db.Column(db.String)
    interests = db.relationship('OwnersInterests', back_populates='owner')


class Interests(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    owners = db.relationship('OwnersInterests', back_populates='interest')

but now I'm wondering about approach, how to prepare a sqlalchemy query with marshmallow schema. Any thoughts?

EDIT :

My current marshmallow schema looks like:

class InterestSchema(ma.ModelSchema):
    class Meta:
        model = Interests
        exclude = ('owners',)


class OwnerSchema(ma.ModelSchema):
    interests = ma.Nested(InterestSchema, many=True)

    class Meta:
        model = Owners

Answer 1

This schema gives you something quite similar to your specification:

from marshmallow import Schema, fields


class InterestSchema(Schema):
    class Meta:
        fields = ('id', 'name')
        ordered = True


class OwnerInterestSchema(Schema):
    interest = fields.Nested(InterestSchema)

    class Meta:
        fields = ('id', 'interest', 'active')
        ordered = True


class OwnerSchema(Schema):
    interests = fields.Nested(OwnerInterestSchema, many=True)

    class Meta:
        fields = ('id', 'name', 'mobile', 'interests')
        ordered = True

You can then serialise your data like this (note that my model doesn't have exactly the same name as yours):

>>> from app.serialisation import OwnerSchema
>>> from app.models import Owner
>>> data = OwnerSchema().dump(Owner.query.get(1))
>>> from marshmallow import pprint
>>> pprint(data)
{"id": 1, "name": "John", "mobile": "07123456789", "interests": [{"interest": {"id": 1, "name": "Economics"}, "active": true}, {"interest": {"id": 2, "name": "Poetry"}, "active": true}, {"interest": {"id": 3, "name": "Sport"}, "active": false}]}

Let me just indent that output so you can see what's going on:

{
  "id": 1,
  "name": "John",
  "mobile": "07123456789",
  "interests": [
    {
      "interest": {
        "id": 1,
        "name": "Economics"
      },
      "active": true
    },
    {
      "interest": {
        "id": 2,
        "name": "Poetry"
      },
      "active": true
    },
    {
      "interest": {
        "id": 3,
        "name": "Sport"
      },
      "active": false
    }
  ]
}

You can adapt this to use the model-plus-exclude paradigm if you want. And if you really want that "_embedded" field in your JSON, you probably need a custom field, as described here.

You could also use custom fields to flatten your interests and put the "active" field on the same level as "id" and "name", but I think that would be misleading.