how to query data inside nested data in flask_sqlalchemy

Question

i using flask_marshmallow here to show out my json response to frontend,

i have i table like bellow

class PostImgList(db.Model):
    __tablename__ = 'boat_img_list'
    id = db.Column(db.Integer, primary_key=True)
    link = db.Column(db.Text, nullable=False)
    main = db.Column(db.Boolean, default=False)
    boat_id = db.Column(db.Integer, db.ForeignKey("boat.id"))

class Post(db.Model):
    __tablename__ = 'boat'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(200))
    seat_capacity = db.Column(db.Integer)
    img_list = db.relationship(PostImgList, backref='boat', lazy=True)

i can get whole my data with :

class PostImgListSchema(ma.ModelSchema):
    class Meta:
        model = PostImgList

class PostSchemaClass(ma.Schema):
    class Meta:
        fields = ('id', 'name', 'seat_capacity', 'img_list')
    img_list = ma.Nested(PostImgListSchema, many=True, only='link')


schema = PostSchemaClass(many=True)

data = Post.query.all()
data = schema.dump(data).data
return data

and frontend can get the data like below

"data": [
    {
      "seat_capacity": 1,
      "id": 1,
      "name": "mantapt",
      "img_list": [
        "fffadweda",
        "fffadweda",
        "fffadweda",
        "fffadweda",
        "fffadweda"
      ]
    },
    {
      "seat_capacity": 10,
      "id": 2,
      "name": "keren",
      "img_list": [
        "fffadweda"
      ]
    }
  ]

i know it's set with many=True in PostImgListSchema, i just wanna some data of that nested schema with a query filter in just one result, so i have to set many=False for PostImgListSchema ? so, how to write PostImgListSchema schema ?

i just wanna show out where of my PostImgList with filter main field value that is True, simply it's similar like bellow :

"data": [
    {
      "seat_capacity": 1,
      "id": 1,
      "name": "mantapt",
      "img_list": "fffadweda"
    },
    {
      "seat_capacity": 10,
      "id": 2,
      "name": "keren",
      "img_list": "fffadweda"
    }
  ]

thanks :)

Answer 1

Using primaryjoin or secondaryjoin (to specify your join condition) in combination with uselist=False should do the trick (see here for details).