For a restaurant application I am trying to set up a Schema in SQLAlchemy with orders and orderlines per order. I am using Flask-SQLAlchemy for the model creation and Flask-Marshmallow for the serialization.
I would like to retrieve all orders for a specific table and show the orderlines with their product information. The table information is showing on the JSON result of the order but table has a 1 to 1 relationship with order and the orderlines have a many to 1 relationship with order. How could I can show the orderlines and their information in the order by using the foreign key of order in the orderlines table?
Imports and setup
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
app = Flask(__name__)
basedir = os.path.abspath(os.path.dirname(__file__))
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///" + \
os.path.join(basedir, 'restuarant.db')
db = SQLAlchemy(app)
ma = Marshmallow(app)
SQLAlchemy Models Order, Orderlines, Table & Product
class Order(db.Model):
__tablename__ = 'Orders'
order_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
order_date = db.Column(db.DateTime, default=datetime.utcnow)
total_price = db.Column(db.Float)
table_id = db.Column(db.Integer, db.ForeignKey(
'Tables.table_id'), nullable=False)
order_items = db.relationship(
'Order_item', backref=db.backref('order', lazy='joined'))
class Order_item(db.Model):
__tablename__ = 'Order_items'
order_item_id = db.Column(
db.Integer, primary_key=True, autoincrement=True)
quantity = db.Column(db.Integer)
order_id = db.Column(db.Integer, db.ForeignKey(
'Orders.order_id')) # nullable = False
product_id = db.Column(db.Integer, db.ForeignKey(
'Products.product_id')) # nullable = False
class Table(db.Model):
__tablename__ = 'Tables'
table_id = db.Column(db.Integer, primary_key=True)
table_name = db.Column(db.String(20))
orders = db.relationship(
'Order', backref=db.backref('table', lazy='joined'))
class Product(db.Model):
__tablename__ = 'Products'
product_id = db.Column(db.Integer, primary_key=True)
product_name = db.Column(db.String(50))
description = db.Column(db.String(250))
price = db.Column(db.Float)
order_items = db.relationship(
'Order_item', backref=db.backref('product', lazy='joined'))
Marshmallow Schemas
class ProductSchema(ma.SQLAlchemySchema):
class Meta:
model = Product
# Fields to expose
product_id = ma.auto_field()
product_name = ma.auto_field()
description = ma.auto_field()
price = ma.auto_field()
product_schema = ProductSchema()
products_schema = ProductSchema(many=True)
class TableSchema(ma.SQLAlchemySchema):
class Meta:
model = Table
# Fields to expose
table_id = ma.auto_field()
table_name = ma.auto_field()
table_schema = TableSchema()
tables_schema = TableSchema(many=True)
class Order_ItemSchema(ma.SQLAlchemySchema):
class Meta:
model = Order_item
# Fields to expose
order_item_id = ma.auto_field()
quantity = ma.auto_field()
item_status = ma.auto_field()
product = ma.Nested(ProductSchema)
order_item_schema = Order_ItemSchema()
order_items_schema = Order_ItemSchema(many=True)
class OrderSchema(ma.SQLAlchemySchema):
class Meta:
model = Order
# Fields to expose
order_id = ma.auto_field()
order_date = ma.auto_field()
total_price = ma.auto_field()
#How to set up the items schema in Marshmallow?
items = ma.List(ma.Nested(lambda: Order_ItemSchema()))
table = ma.Nested(TableSchema)
order_schema = OrderSchema()
orders_schema = OrderSchema(many=True)
Route for order
@ app.route("/orders", methods=["GET", "POST"])
def orders():
if request.method == "POST":
pass
else:
orders = Order.query.filter_by(table_id=1).all()
return jsonify(orders_schema.dump(orders))
JSON output
[
{
"order_date": "2022-04-10T13:34:33.891639",
"order_id": 1,
"table": {
"table_id": 1,
"table_name": "Test table"
},
"total_price": 4.04
},
{
"order_date": "2022-04-10T19:10:48.735628",
"order_id": 2,
"table": {
"table_id": 1,
"table_name": "Test table"
},
"total_price": 2.02
}
]
Wanted Result
[
{
"order_date": "2022-04-10T13:34:33.891639",
"order_id": 1,
"table": {
"table_id": 1,
"table_name": "Test table"
},
"items": [
{
"order_item_id": 1,
"quantity": 2,
"order_id": 1,
"product_id": 1
},
{
"order_item_id": 2,
"quantity": 4,
"order_id": 1,
"product_id": 2
}
],
"total_price": 4.04
}
]
