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 :)

Mikhail Berlinkov · Accepted Answer · 2019-08-26 13:04:49Z

2

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

answered Aug 26, 2019 at 13:04

Mikhail Berlinkov

1,63410 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arian Saputra Over a year ago

if i do that, i can't filter my PostImgList

Mikhail Berlinkov Over a year ago

Why can't you? You can include it in join condition.

Arian Saputra · Accepted Answer · 2019-08-26 14:11:55Z

0

solved !!!

i just create a protperty in class model Post

class Post(db.Model):
...
...
    @property
    def image(self):
        img = PostImgList.query.filter_by(main=True).first()
        if not img:
            return ""
        return img.link

edited Aug 26, 2019 at 14:11

answered Aug 26, 2019 at 13:56

Arian Saputra

4217 silver badges16 bronze badges

2 Comments

Mikhail Berlinkov Over a year ago

Be aware that this property will be recomputed every time you access it and it is read-only.

Arian Saputra Over a year ago

Thanks @MikhailBerlinkov, now I just realized

Collectives™ on Stack Overflow

how to query data inside nested data in flask_sqlalchemy

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related