Flask Sqlalchmey - Marshmallow Nested Schema 连接过滤器( where )条件失败 [英] Flask Sqlalchmey - Marshmallow Nested Schema fails for joins with filter ( where ) conditions

问题描述

我目前正在制作一个带有 get 请求的 API，以返回模型的连接 json 数据，这些数据与使用flask、sqlalchemy 和flask-sqlalchemy 以及flask-marshmallow 有关系.我在使用 while select all 时没有问题，选择特定数据但我在使用连接时有问题.任何人都可以指导我犯了什么错误.

数据库模型

class orderinfo(db.Model):__tablename__ = '订单信息'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)数量 = db.Column(db.Integer, nullable = False)托盘 = db.relationship('Traydetails', backref="orderinfo",lazy='dynamic')类托盘详细信息(db.Model):__tablename__ = '托盘详细信息'id= db.Column(db.Integer,autoincrement=True,primary_key=True)托盘编号 = db.Column(db.String, unique=True)orderid= db.Column(db.Integer, db.ForeignKey('orderinfo.id'))托盘状态 = db.Column(db.String , nullable = False)测试 = db.relationship('Testinfo', backref="Traydetails",lazy='dynamic')类测试信息(db.Model):__tablename__ = '测试信息'id = db.Column(db.Integer,autoincrement=True,primary_key=True)托盘ID = db.Column(db.Integer, db.ForeignKey('traydetails.id'))test_started = db.Column(db.DateTime, nullable = True)test_ended = db.Column(db.DateTime, nullable = True)描述 = db.Column(db.String, nullable = True

架构

class orderinfoSchema(ma.Schema):元类:fields =('ordernumber','orderdate','storage_duration','quantity','trays')有序 = 真托盘 = fields.Nested(TraydetailsS​​chema,many= True)order_schema = orderinfoSchema()订单架构 = 订单信息架构(很多 = 真)类 TraydetailsS​​chema(ma.Schema):元类:fields = ('traynumber','traystatus','tests','description')有序 = 真测试 = fields.Nested(TestinfoSchema,many= True)托盘架构 = 托盘详细信息架构()托盘模式 = 托盘详细信息架构(许多 = 真)类 TestinfoSchema(ma.Schema):元类:fields =('trayid','test_started','test_ended','description')有序 = 真test_schema = TestinfoSchema()tests_schema = TestinfoSchema(many = True)

数据库:

选择订单信息，托盘详细信息来自订单信息内部连接 ​​Traydetails ON Traydetails.orderid = orderinfo.id 其中 Traydetails.traystatus = 'Reserved';

我正在尝试实现上述 sql 语句，它提供了与我在 Postgres 中寻找的完全相同的结果.但是当我用棉花糖和 sqlalchmey 尝试相同的场景时，它无法删除过滤器，并且 Traydetails 中的所有数据都出现在嵌套模式中，我只想在嵌套模式中实现过滤器数据

API:

我在第一个场景中尝试了多个查询，我得到了嵌套架构中的所有数据，但未能删除过滤条件

@app.route('/traystatus/',methods=['GET'])定义托盘状态(状态):Loaded=orderinfo.query.join(Traydetails,Traydetails.orderid==orderinfo.id).filter(Traydetails.traystatus==status).all()结果 = orders_schema.dump(加载)返回 orders_schema.jsonify(result)

第二个场景显示空列表

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

输出2:

<预><代码>[{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}，{}]

解决方案

您的查询具有联接，因此创建 SQLAlchemy 对象的元组.摆脱连接，只需查询通过关系和外键链接到其他表的 OrderInfo 模型(使用 PascalCase).因此，您可以获得正确填充 Marshmallow 模式所需的所有数据.查看此线程以获得一个很好的示例:Flask Marshmallow 序列化具有额外字段的多对多关系

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 :

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

解决方案

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

这篇关于Flask Sqlalchmey - Marshmallow Nested Schema 连接过滤器( where )条件失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！