[FIXED] One to many query using marshmallow for response not working

Issue

I'm trying to make a query and the response as follows:

    get_data = request.get_json()
    email = get_data.get('email')
    result = User.query.join(User_Preference).filter(User.email==email).first()
    dump_data = developer_schema.dump(result)
    return jsonify({'data' : dump_data})

from the following two tables defined as follows:

class User_Preference(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey(User.id))
    preference = db.Column(db.String(45))

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(50), unique=True)
    password = db.Column(db.String(256))
    experience = db.Column(db.Integer)
    avatar = db.Column(db.String(256))
    revenue = db.Column(db.Integer)
    preferences = relationship("User_Preference")

These are the marshmallow schemas I am using:

class DeveloperPreferences(ma.Schema):
    class Meta:
        fields = ('user_id', 'preference')

class DeveloperSchema(ma.Schema):
  class Meta:
    fields = ('id', 'email', 'avatar')
    #model = User
    preferences = ma.Nested(DeveloperPreferences, many = True)

However, the return I am getting is as follows:

{
    "data": {
        "avatar": "example.com",
        "email": "[email protected]",
        "id": 10
    }
}

That is only the user part of the query is being returned not the preferences. Anyone have any idea on how to solve this?

Solution

In order to also display the preferences in the result, it is necessary to also specify the field in the fields to be displayed.
When defining the nested fields, you slipped into the meta specification with the indentation. However, the fields are set at the schema level.

class DeveloperPreferenceSchema(ma.Schema):
    class Meta:
        fields = ('user_id', 'preference')

class DeveloperSchema(ma.Schema):
    class Meta:
        fields = ('id', 'email', 'avatar', 'preferences')
    preferences = ma.Nested(DeveloperPreferenceSchema, many=True)

The result should then be as follows.

{
  "data": {
    "avatar": "example.com", 
    "email": "[email protected]", 
    "id": 10, 
    "preferences": [
      {
        "preference": "anything", 
        "user_id": 10
      }, 
      {
        "preference": "something", 
        "user_id": 10
      }
    ]
  }
}

Answered By - Detlef