Serializing one to many relationships nested json with flask marshmallow

Question

I have tried to read existing questions about serializing with flask-Marshmallow, but I can't seem to get what I what, I would like to know what am missing :

I want get such a response :

{
   "data": [ {"name": "Netherlands tour",
    "description": "This is a fascinating tour to take ...",
    "price": 30.0,
    "available_dates":[
        {
            "date": "2018-05-05"
        },
        {
            "date": "2018-07-07"
        }
    ],
    "destinations":[
        {
            "location":"A",
            "tour_type":"Adventure",
            "danger_type":"Low"
        },
        {
             "location":"B",
            "tour_type":"Leisure",
            "danger_type":"Medium"
        }
    ],
    "capacity": 30},
  ...
]
}

But I am not getting it, am just getting this :

{
  "data": [
    {
      "capacity": 30,
      "description": "This is a fascinating tour to take",
      "name": "Europe tour",
      "price": 30.0
    },
    {
      "capacity": 30,
      "description": "This is a fascinating tour to take",
      "name": "Europe tour",
      "price": 30.0
    },
    {
      "capacity": 30,
      "description": "This is a fascinating tour to take",
      "name": "Europe tour",
      "price": 30.0
    },
    {
      "capacity": 30,
      "description": "This is a fascinating tour to take",
      "name": "Europe tour",
      "price": 30.0
    }
]

Below is my model :

class TourPackages(db.Model):
    __tablename__ = 'tourpackage'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    description = db.Column(db.TEXT)
    price = db.Column(db.Float)
    destinations = db.relationship('Destinations', backref='destination_id', lazy='dynamic')
    available_dates = db.relationship('AvailableDates', backref='available_date_id', lazy='dynamic')
    capacity = db.Column(db.Integer)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)


class Destinations(db.Model):
    __tablename__ = 'destinations'

    id = db.Column(db.Integer, primary_key=True)
    tour_Packages = db.Column(db.Integer, db.ForeignKey('tourpackage.id'))
    location = db.Column(db.String(50))
    tour_type = db.Column(db.String(50))
    danger_type = db.Column(db.String(50))


class AvailableDates(db.Model):
    __tablename__ = 'availabledates'

    id = db.Column(db.Integer, primary_key=True)
    date_available = db.Column(db.String(50))
    tour_date = db.Column(db.Integer, db.ForeignKey('tourpackage.id'))

then, this is my marshmallow schema :

class DestinationSchema(ma.Schema):
    class Meta:
        model = Destinations
        sqla_session = Session
        fields = ('id', 'location', 'danger_type')



class AvailableDatesSchema(ma.Schema):
    class Meta:
        model = AvailableDates
        sqla_session = Session
        fields = ('id', 'date_')


class TourPackagesSchema(ma.Schema):
    class Meta:
        model = TourPackages
        fields = ('id', 'name', 'description', 'price', 'capacity')

    destiantion = fields.Nested(DestinationSchema, many=True)
    available = fields.Nested(AvailableDatesSchema, many=True)

then, in my GET request method this is my code :

def get_all_tours(self):
    tours = db.session.query(TourPackages.name, TourPackages.description, TourPackages.price,
                             TourPackages.capacity,
                             Destinations.location,
                             Destinations.tour_type,
                             Destinations.danger_type, AvailableDates.date_available).join(
        Destinations).join(AvailableDates).all()


    tour_schema = TourPackagesSchema(many=True)


    dump_data = tour_schema.dump(tours)

    output = jsonify({'data' : dump_data})
    return output

What am I missing to serialize nested json response ?

Answer 1

I hope my answer is helpful.

Models:

class TourPackage(db.Model):
    __tablename__ = 'tour_packages'

    id = db.Column(db.Integer, primary_key=True)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)
    name = db.Column(db.String(50))
    description = db.Column(db.TEXT)
    price = db.Column(db.Float)
    capacity = db.Column(db.Integer)
    destinations = db.relationship('Destination', backref='tour_package', lazy='dynamic')
    available_dates = db.relationship('AvailableDate', backref='tour_package', lazy='dynamic')

class Destination(db.Model):
    __tablename__ = 'destinations'

    id = db.Column(db.Integer, primary_key=True)
    tour_package_id = db.Column(db.Integer, db.ForeignKey('tour_packages.id'))
    location = db.Column(db.String(50))
    tour_type = db.Column(db.String(50))
    danger_type = db.Column(db.String(50))

class AvailableDate(db.Model):
    __tablename__ = 'available_dates'

    id = db.Column(db.Integer, primary_key=True)
    tour_package_id = db.Column(db.Integer, db.ForeignKey('tour_packages.id'))
    date_available = db.Column(db.String(50))

Schemes:

class DestinationSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Destination

class AvailableDateSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = AvailableDate


class TourPackageSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = TourPackage

    destinations = ma.Nested(DestinationSchema, many=True)
    available_dates = ma.Nested(AvailableDateSchema, many=True)

Route:

@blueprint.route('/')
def index():
    tours = TourPackage.query.all()
    tours_schema = TourPackageSchema(many=True)
    dump_data = tours_schema.dump(tours)
    return jsonify({'data' : dump_data})