I am trying to optimize on the performance of my models, however I can see that FastAPI is loading the relations of my model PurchaseOrderPart as lazy.. This is causing a n+1 problem, which is a big problem. Is there any way I can make FastAPI load the relations as joined? When I am not using the response model parameter I get most of the data I need, but not all the relations.
class PurchaseOrderPart(Base):
__tablename__ = "purchase_order_part"
id = Column(BigInteger, primary_key=True, index=True)
quantity = Column(Float)
ordered_at = Column(DateTime, nullable=True)
expected_at = Column(DateTime, nullable=True)
confirmed_at = Column(DateTime, nullable=True)
delivered_at = Column(DateTime, nullable=True)
invoiced_at = Column(DateTime, nullable=True)
price_dkk = Column(BigInteger, server_default="0")
deviation = Column(String, nullable=True)
supplier_part_id = Column(BigInteger, ForeignKey("supplier_part.id"))
supplier_id = Column(BigInteger, ForeignKey("supplier.id"))
project_reference_id = Column(BigInteger, ForeignKey(
"project_reference.id"), nullable=True)
stockpile_part_id = Column(BigInteger, ForeignKey(
"stockpile_part.id"), nullable=True)
purchase_order_id = Column(BigInteger, ForeignKey(
"purchase_order.id"), nullable=True)
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, server_default=func.now(),
onupdate=func.current_timestamp())
supplier_part = relationship(
"SupplierPart", uselist=False, foreign_keys=[supplier_part_id], lazy="selectin")
supplier = relationship("Supplier", uselist=False,
foreign_keys=[supplier_id], lazy="selectin")
project_reference = relationship(
"ProjectReference", uselist=False, foreign_keys=[project_reference_id], lazy="selectin")
stockpile_part = relationship(
"StockpilePart", uselist=False, foreign_keys=[stockpile_part_id], lazy="selectin")
purchase_order = relationship(
"PurchaseOrder", uselist=False, foreign_keys=[purchase_order_id], lazy="selectin")
purchase_order_part_notes = relationship(
"PurchaseOrderPartNote", back_populates="purchase_order_part", lazy="selectin")`
class PurchaseOrderPartRelation(PurchaseOrderPart):
purchase_order_part_notes: Optional[List["PurchaseOrderPartNote"]] = None
supplier: Optional[SupplierBase] = None
supplier_part: Optional[SupplierPart] = None
purchase_order: Optional[PurchaseOrder] = None
project_reference: Optional[ProjectReference] = None
stockpile_part: Optional[StockpilePartRelation] = None
part: Optional["Part"]
#################################
# Not Confirmed
#################################
@router.get(
"/not_confirmed",
summary="List all purchase order parts that are ordered but not confirmed",
status_code=status.HTTP_200_OK,
)
def get_not_confirmed_parts(request: Request, end_days_from_now: int = 7,
filter_and: Optional[List[str]] = Query(None),
filter_or: Optional[List[str]] = Query(None),
sort: Optional[List[str]] = Query(None),
page: int = 1,
size: int = 1000,
current_user: User = Depends(
get_current_user),
db: Session = Depends(get_db)):
with tracer.start_span("traces for not confirmed parts") as current_span:
query = db.query(models.PurchaseOrderPart, models.SupplierPart, models.Part).\
filter(and_(
models.PurchaseOrderPart.confirmed_at.is_(None),
models.PurchaseOrderPart.delivered_at.is_(None),
models.PurchaseOrderPart.ordered_at <= ordered_date,
models.PurchaseOrderPart.supplier_part_id == models.SupplierPart.id,
models.Part.primary_supplier_part_id == models.SupplierPart.id
))
# logger.debug(str(query))
# return query
with tracer.start_span("get filtered page trace") as current_span:
results = get_filtered_page(
db,
query,
filter_or,
filter_and,
sort,
page,
size)
return results