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

Can you give an example of what you want? What's the relationship between the database and the serialization? — Tommy Herbert, Oct 15 '19 at 13:37
@TommyHerbert The relationship between the models is many to many with association table included (see snippet with models). I added also marshmallow schemas in my edit. — sorryMike, Oct 16 '19 at 07:42
It sounds as though you're trying to use SQLAlchemy to query the filesystem rather than a database. Is that right? If so, why are you trying to do that? — Tommy Herbert, Oct 17 '19 at 09:14
The data is the fact in the db in my case, and I would like to prepare proper flask response based on example json in the beginning of my question. — sorryMike, Oct 17 '19 at 10:23
Oh I think I see now. So step 1 is to retrieve the objects from the database, and step 2 is to use Marshmallow to generate the JSON data from the objects? — Tommy Herbert, Oct 17 '19 at 11:56

Tommy Herbert · Answer 1 · 2019-10-31T15:30:27.467

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.

Your idea related to creating Association schema is good. I used post_dump decorator for customize wrapping the interests part into _embedded key. Official Marshmallow example - https://marshmallow.readthedocs.io/en/stable/extending.html#example-enveloping — sorryMike, Oct 31 '19 at 16:35
This example was extremely valuable to me for realizing that with the query of a single model you can extract also all data from related tables to avoid the Tuples that SQLAlchemy returns if you make a join in your query — Greg Holst, Jun 11 '21 at 21:42

Flask Marshmallow serialize many to many relation with extra field

1 Answers1

Linked