pythonsqlalchemymarshmallow

Filter Nested field in Flask Marshmallow


I want to filter the nested field with is_active column as True in Marshmallow 3 Consider following scenario I have 3 tables

users (id, name)
organizations (id, name)
organization_user(id, organization_id, user_id, is_active)

Now I'm trying to print all the organization with its members who are active. (There are some active and inactive members)

I have following schema

class OrganizationSchema(ma.ModelSchema):
    members_list = fields.Nested(OrgnizationUserSchema, many=True, exclude=('checklist', ))

class OrgnizationUserSchema(ma.ModelSchema):
    user_list = fields.Nested(UserSchema)

Now in my action following is the code

organization_schema = OrganizationSchema(many=True)
#Query for list of organization
organization_list = Organization.query.all()
organization_schema.dump(organization_list)

Following is Output

[
    {
    'id': 1,
    'name': 'abc',
    'members_list': [
        {'id':1, 'organization_id': 1, 'user_id':1, 'is_active':True},
        {'id':1, 'organization_id': 1, 'user_id':2, 'is_active':False}
        ]
    }
]

I want output with member who has 'is_active':True as follows

[
    {
    'id': 1,
    'name': 'abc',
    'members_list': [
        {'id':1, 'organization_id': 1, 'user_id':1, 'is_active':True}
        ]
    }
]

Marshmallow provides a decorator @post_dump. Problem here is Query brings all data and then we filter it with decorator @post_dump. But the flow should be like, while querying there should be some way to filter the data and not post query filtering.


Solution

  • I went the other way. I have cloths, designs and remainders. For each fabric I need to get all the designs, and for each design will get the remainders for the specified city.

    class ClothSchema(Schema):
        id = fields.Integer(dump_only=True)
        name = fields.String(validate=not_blank)
        type = fields.Nested(ClothTypeSchema)
        designs = fields.Nested(DesignSchema, many=True)
    
    class DesignSchema(Schema):
        id = fields.Integer(dump_only=True)
        name = fields.String(validate=not_blank)
        remainders = fields.Nested(RemainderSchema, many=True)
    
    class RemainderSchema(Schema):
        id = fields.Integer(dump_only=True)
        value = fields.String()
        city = fields.String()
    

    I get the data I need in the controller so that they are not requested on the go.

        db.session.query(Cloth)
        .join(Cloth.designs)
        .join(Design.remainders)
        .filter(Remainder.city == city)
        .options(contains_eager("designs").contains_eager("remainders"))
    

    As a result, I get all the cloths and all the designs for which the remainders are given. If no design remainders are indicated, it will not be displayed.

    {
      "attributes": {
        "designs": {
          "data": [
            {
              "attributes": {
                "name": "Amely 10 ", 
                "remainders": {
                  "data": [
                    {
                      "attributes": {
                        "city": "CityEnum.MOSCOW", 
                        "value": "333"
                      }, 
                      "id": 9318, 
                      "type": "remainder"
                    }
                  ]
                }
              }, 
              "id": 365, 
              "type": "design"
            } 
          ], 
          "links": {
            "self": "/designs"
          }
        }, 
        "name": "Amely", 
        "rapport": null, 
        "type": {} 
      }, 
      "id": 22, 
      "type": "cloth"
    }