FastAPI, Pydantic joinedload instead of lazy

Viewed 526

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
0 Answers
Related