[FIXED] Sqlalchemy order_by hybrid_property orders by something else

Issue

So, I have a Product class and a User class. A User is the seller of a Product, and can have many products. The Product.price is the price without VAT, and each User have a flag in the User table is_vat, to state if the price showed externally shall have VAT added to it or not.

So I wanted to create a query which is ordered by price, and decided to create a hybrid_property (show_price) so that I would be able to order_by the price showed externally. The hybrid_property checks if the seller have the is_vat flag checked, and then calculates the outward showing price depending on whether VAT should be added or not.

So, the problem is, the query results come out ordered on Product.price, instead of Product.show_price, and I don't understand what on earth I am doing wrong?

This is the query I am using (Flask-sqlalchemy and old sqlalchemy syntax):

products_query = db.session.query(Product)
products = products_query.order_by(Product.show_price.desc()).all()

This is the Product class with the hybrid_property:

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    price = db.Column(db.Integer)
    seller_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    
    @hybrid_property
    def show_price(self):
        is_vat = db.session.query(User.is_vat).filter(User.id == self.seller_id).first()[0]
        if is_vat == True:
            return (self.price * 1.25)
        if is_vat == False or is_vat == None:
            return (self.price * 1.0)

Edit: Based on the response from @detlef I found out I had to create an expression for the hybrid_property. To first try to make a solution without the association_proxy, I made the following expression, which allows me to use Product.show_price in order_by. I believe the expression has to return a query object which calculates the value, not the value in itself.

@show_price.expression
def show_price(cls):
    return db.session.query(((db.func.coalesce(User.is_vat, False).cast(db.Integer) * 0.25) + 1) * cls.price).where(User.id == cls.seller_id) 

Solution

The problem is that the Python code cannot be used directly in the SQL statement. A translation is necessary for the condition to work.

One way to accomplish your goal is to add the relationship to the seller and set an association_proxy to the is_vat property. Then add an expression for the hybrid_property that uses the case statement. This is used to create the actual SQL statement for the query.

From then on, sorting should work by value depending on the condition.

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy import case

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    price = db.Column(db.Integer)
    seller_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    seller = db.relationship('User')
    is_vat = association_proxy('seller', 'is_vat')

    @hybrid_property
    def show_price(self):
        return (self.price * 1.25) if self.is_vat else (self.price * 1.0)

    @show_price.expression
    def show_price(cls):
        return case(
            (cls.is_vat == True, cls.price * 1.25),
            else_=cls.price * 1.0
        )

Answered By - Detlef