Flask Sqlalchmey - Marshmallow Nested Schema fails for joins with filter ( where ) conditions

Question

I'm currently making an API with get request to return a joined json data of models which has a relationship using flask, sqlalchemy and flask-sqlalchemy and flask-marshmallow. I have no issues when I use while select all , select particular data but i have issue while using joins. Can anyone guide me what mistakes i made.

database models

class orderinfo(db.Model):
        __tablename__ = 'orderinfo'
        id = db.Column(db.Integer,autoincrement=True, primary_key=True)
        ordernumber = db.Column(db.String, unique=True)
        orderdate = db.Column(db.DateTime, nullable = False)
        storage_duration = db.Column(db.String(50),nullable=False)
        quantity = db.Column(db.Integer, nullable = False)
        trays = db.relationship('Traydetails', backref="orderinfo",lazy='dynamic')
    
    
    class Traydetails(db.Model):
        __tablename__ = 'traydetails'
        id= db.Column(db.Integer,autoincrement=True, primary_key=True)
        traynumber = db.Column(db.String, unique=True)
        orderid= db.Column(db.Integer, db.ForeignKey('orderinfo.id'))
        traystatus = db.Column(db.String , nullable = False)
        tests = db.relationship('Testinfo', backref="Traydetails",lazy='dynamic')
    
    class Testinfo(db.Model):
        __tablename__ = 'testinfo'
        id =  db.Column(db.Integer,autoincrement=True, primary_key=True)
        trayid = db.Column(db.Integer, db.ForeignKey('traydetails.id'))
        test_started = db.Column(db.DateTime, nullable = True)
        test_ended = db.Column(db.DateTime, nullable = True)
        description = db.Column(db.String, nullable = True

Schema

class orderinfoSchema(ma.Schema):
    class Meta:
        fields =('ordernumber','orderdate','storage_duration','quantity','trays')
        ordered = True
    trays = fields.Nested(TraydetailsSchema,many= True)


order_schema = orderinfoSchema()
orders_schema = orderinfoSchema(many = True)


class TraydetailsSchema(ma.Schema):
    class Meta:
        fields = ('traynumber','traystatus','tests','description')
        ordered = True
    tests = fields.Nested(TestinfoSchema,many= True)

tray_schema = TraydetailsSchema()
trays_schema = TraydetailsSchema(many=True)


class TestinfoSchema(ma.Schema):
    class Meta:
        fields =('trayid','test_started','test_ended','description')
        ordered = True

test_schema = TestinfoSchema()
tests_schema = TestinfoSchema(many = True)

Sql:

SELECT orderinfo,Traydetails FROM orderinfo INNER JOIN Traydetails ON Traydetails.orderid = orderinfo.id where Traydetails.traystatus = 'Reserved';

I'm Trying to achieve the above sql statement and it offers the same exact results that i'm looking for in Postgres. but when i try the same scenario with marshmallow and sqlalchmey it fails to remove the filters and all the data's from Traydetails are appering in the nested schema i just want to achieve the filterdata with in the nested schema

API :

I tried with multiple query in First scenario I get all the data in nested Schema which fails to remove the filter condition

@app.route('/traystatus/<status>',methods=['GET'])
def traystatus(status):
loaded=orderinfo.query.join(Traydetails,Traydetails.orderid==orderinfo.id).filter(Traydetails.traystatus==status).all() 
result = orders_schema.dump(loaded)
return orders_schema.jsonify(result)

Second Scenario its shows empty lists

loaded = db.session.query(orderinfo,Traydetails).join(orderinfo).filter(Traydetails.traystatus == status,Traydetails.orderid == orderinfo.id).all()

output 2 :

[
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}, 
  {}
]

Answer 1

Your query has a join and therefore creates Tuples of SQLAlchemy objects. Get rid of the join and just query the OrderInfo model (use PascalCase) that is linked via relationships and foreign keys to the other tables. Therefore you get all data that is needed for the Marshmallow schemas to get filled properly. Check this thread out for a nice example: Flask Marshmallow serialize many to many relation with extra field