How do you query @hybrid_property with an aggregate function in SQLAlchemy?

Question

here is my sanerio: i have a model named Order: i have some fields like that:

@hybrid_property
    def final_total(self):
        total = self.subtotal

        if self.final_discount_amount is not None:
            total -= self.final_discount_amount

        if self.final_tax_cost is not None:
            total += self.final_tax_cost

        if self.final_tip is not None:
            total += self.final_tip

        if self.service_fee is not None:
            total += self.service_fee

        return total

    @final_total.expression
    def final_total(cls):
        return func.sum(cls.final_total).label("final_total")

i want to get final_total of a record from database using query obj.

here is my query:

session.query(Order.final_total).filter_by(order.id=1).first()

i am getting error like:

python3.8/site-packages/sqlalchemy/ext/hybrid.py", line 1090, in _expr
    return ExprComparator(cls, expr(cls), self)
RecursionError: maximum recursion depth exceeded

Answer 1

It is possible to do it using hybrid_property but you can face huge performance problem because this final_total calculation will have to be done during query runtime... Better option is to add new column final_total to you model (and db) and make this calculation when any of elements (e.g. final_tip, etc.) is changed and the result save in this new column.

However, if hybrid property performance is enough for you, this is implementation that should work:

from sqlalchemy import func
    
    @hybrid_property
    def final_total(self):
        total = self.subtotal
        if self.final_discount_amount is not None:
            total -= self.final_discount_amount
        if self.final_tax_cost is not None:
            total += self.final_tax_cost
        if self.final_tip is not None:
            total += self.final_tip
        if self.service_fee is not None:
            total += self.service_fee
        return total

    @final_total.expression
    def final_total(cls):
        return (
            cls.subtotal +
            func.coalesce(cls.final_discount_amount, 0) +
            func.coalesce(cls.service_fee, 0) + 
            func.coalesce(cls.final_tax_cost, 0) +
            func.coalesce(cls.final_tip, 0)
        )