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 :
[
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{}
]
