I am new to sanic and SQLAlchemy, I am implementing c2c ecom. I have a many-to-many relationship between orders and products that is joined by order_products table with quantity as an extra field on the join table (i.e. order_products)
class Order(Base):
__tablename__ = "orders"
uuid = Column(String(), primary_key=True)
userId = Column(String(), nullable=False)
status = Column(String(), nullable=False, default="draft")
products = relationship(
"Product", secondary="order_products", back_populates='orders')
order_products = relationship(
"OrderProduct", back_populates="order")
def to_dict(self):
return {"uuid": self.uuid, "userId": self.userId, "status": self.status, "products": [{
"title": product.title,
"price": product.price,
"description": product.description,
"uuid": product.uuid,
"userId": product.userId,
"quantity": product.order_products.quantity
} for product in self.products]}
---
class Product(Base):
__tablename__ = "products"
uuid = Column(String(), primary_key=True)
title = Column(String(), nullable=False)
price = Column(FLOAT(), nullable=False)
description = Column(String(), nullable=False)
userId = Column(String(), nullable=False)
orders = relationship(
"Order", secondary="order_products", back_populates='products', uselist=False)
order_products = relationship(
"OrderProduct", back_populates="product")
---
class OrderProduct(Base):
__tablename__ = "order_products"
id = Column(Integer, primary_key=True)
orderId = Column(String, ForeignKey("orders.uuid"))
productId = Column(String, ForeignKey("products.uuid"))
quantity = Column(Integer)
# Define the relationships to the Order and Product tables
order = relationship("Order", back_populates="order_products")
product = relationship("Product", back_populates="order_products")
A user here is a buyer as well as seller.
I need to extract all the orders for a seller's product that is being paid for (i.e Order.status == 'complete')and populate the products and quantity of those products.
[
{
"uuid": "order_Id_abc",
"userId": "user_Id_def", ## USER who paid for the order
"status": "complete",
"products": [
{
"title": "One Piece t-shirt",
"price": 900.0,
"description": "Luffy at laugh tail island",
"uuid": "product_Id",
"userId": "current_user_Id", ## Sellers Id
"quantity": 2
},
## End up getting products of other Sellers, which was the part of this order.
]
}
]
To achieve this I used following query
from sqlalchemy.orm import selectinload
from sqlalchemy.sql.expression import and_
from sqlalchemy.future import select
q = (select(Order)
.join(OrderProduct, Order.uuid == OrderProduct.orderId)
.join(Product, OrderProduct.productId == Product.uuid)
.filter(and_((Order.status == 'complete'), (Product.userId == "current_user_id")))
.options(selectinload(Order.products).selectinload(Product.order_products))
.group_by(Order.uuid)
)
I get my desired result, but end up getting other user's products too.
Can't figure out what is wrong here..
Thank you.
I was building query incorrectly.
In order to filter the Products (or any entity) inside selectinload(), we can use and_ Comparator
The correct query to achieve the desired result is :
q = (select(Order)
.join(OrderProduct, Order.uuid == OrderProduct.orderId)
.join(Product, OrderProduct.productId == Product.uuid)
.filter(and_(Order.status == OrderStatus.Completed)
.options(selectinload(Order.products.and_(Product.userId == "current_user_id"))
.selectinload(Product.order_products))
.group_by(Order.uuid)
)
Hope it helps... :)