pythonsqlalchemyflask-sqlalchemyflask-admin

Flask admin - complex sort on hybrid_property


I have a fairly complex hybrid_property. This is a vendor model, which has multiple skuchannels (products). What it does is: Based on the target_stock_duration (e.g. we want to keep items in stock for 4 months) calculate how many units have to be ordered and how much this would cost. This gives us the potential.

class Vendor(db.Model):
    __tablename__ = "vendor"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(150))
    b2c_price_factor = db.Column(db.Float, nullable=False)
    skuchannels = db.relationship("SKUChannel", back_populates="vendor")

    @hybrid_property
    def po_potential(self):
        """This is a "virtual" property that will that can be used in the admin view. It calculates
        the potential value for a comming PO.

        Returns:
            _type_: _description_
        """
        
        potential = 0
        for item in self.skuchannels:
            purchasing_price = item.purchasing_price if item.purchasing_price != None else 0
            
            target_stock_duration = 4

            try:
                to_order = item.average_monthly_sales * target_stock_duration - item.stock_level #calculate how many units we have to order
                if to_order < 0:
                    to_order = 0
            except TypeError:
                to_order = 0
            
            potential = potential + purchasing_price * to_order #calculate how much everything costs

        return potential

well this hybrid_property works just fine, but I would very much like to sort this property. with @po_potential.expression -> well I have no clue how to do this, because in my understanding it should return a select object. Is there any other way?


Solution

  • This should get you started:

    class Vendor(Base):
        ...
        ...
        @po_potential.expression
        def po_potential(cls):
            target_stock_duration = 4
            return (
                select(func.sum(
                    func.ISNULL(SKUChannel.purchasing_price, 0) *
                    func.GREATEST(0, SKUChannel.average_monthly_sales * target_stock_duration - SKUChannel.stock_level, 0)
                ))
                .where(SKUChannel.vendor_id == cls.id)
                .label('po_potential')
            )