Flask +SQLalchemy calculated field filter

Question

Currently I have a class with a hybrid property named calc_totalPrice. In this hybrid property, I try to calculate the total price of something based on a number of items it's related to in another table (through an intermediary table). Here is the class:

class Recipe(db.Model):

    recipeID = Column(Integer, primary_key=True)
    userID = Column(ForeignKey('user.userID'), nullable=False)
    name = Column(String(35), nullable=False)
    description = Column(String(140), nullable=False)

    User = db.relationship('User')

    @hybrid_property
    def calc_totalPrice(self):
        calculatedPrice = func.sum(Ingredient.price).label('price')
        recipeIngredientJoin = Recipe.query.join(IngredientsToRecipe,Recipe.recipeID == IngredientsToRecipe.recipeID).join(Ingredient,IngredientsToRecipe.siin == Ingredient.siin).add_columns(calculatedPrice).group_by(Recipe.recipeID).filter(Recipe.recipeID == self.recipeID).first()
        print(calculatedPrice)
        return calculatedPrice

The method I am trying to run looks as follows:

@app.route('/api/tasks/getrecipetypeinbudget/<float:budget>', methods=['GET'])
def GetRecipeTypeInBudget(budget):
    recipeIngredients = Recipe.query.filter(Recipe.calc_totalPrice <= budget)
    serialiser = RecipeSerializer(many = True)
    result = serialiser.dump(recipeIngredients)

    return jsonify({'Recipe' : result})

Unfortunately, I get the following error:

 * Running on http://0.0.0.0:53827/ (Press CTRL+C to quit)
127.0.0.1 - - [10/Apr/2018 00:27:11] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:11] "GET /static/content/bootstrap.min.css HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:12] "GET /static/scripts/modernizr-2.6.2.js HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:12] "GET /static/scripts/respond.js HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:12] "GET /static/content/site.css HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:12] "GET /static/scripts/bootstrap.js HTTP/1.1" 200 -
127.0.0.1 - - [10/Apr/2018 00:27:12] "GET /static/scripts/jquery-1.10.2.js HTTP/1.1" 200 -
sum(ingredient.price)
[2018-04-10 00:27:27,382] ERROR in app: Exception on /api/tasks/getrecipetypeinbudget/5.0 [GET]
Traceback (most recent call last):
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: aggregate functions are not allowed in WHERE
LINE 3: WHERE sum(ingredient.price) <= 5.0
              ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\_compat.py", line 33, in reraise
    raise value
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\flask\app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\app\views.py", line 46, in GetRecipeTypeInBudget
    result = serialiser.dump(recipeIngredients)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\marshmallow\schema.py", line 481, in dump
    obj = list(obj)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\orm\query.py", line 2889, in __iter__
    return self._execute_and_instances(context)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\orm\query.py", line 2912, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\Samuel.endeva\Google Drive\Projects\Sal\SalApp\SalApp\FlaskWebAPI\env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) aggregate functions are not allowed in WHERE
LINE 3: WHERE sum(ingredient.price) <= 5.0
              ^
 [SQL: 'SELECT recipe."recipeID" AS "recipe_recipeID", recipe."userID" AS "recipe_userID", recipe.name AS recipe_name, recipe.description AS recipe_description \nFROM recipe, ingredient \nWHERE sum(ingredient.price) <= %(param_1)s'] [parameters: {'param_1': 5.0}] (Background on this error at: http://sqlalche.me/e/f405)
127.0.0.1 - - [10/Apr/2018 00:27:27] "GET /api/tasks/getrecipetypeinbudget/5.0 HTTP/1.1" 500 -

I just started using Flask today and while I can sort of understand where I'm going wrong, I don't know how to remedy it. Any ideas or alternatives?

UPDATE: I got a little bit of success. I changed the hybrid property to this:

@hybrid_property
def calc_totalPrice(self):
    calculatedPrice = func.sum(Ingredient.price).label('price')
    recipeIngredientJoin = Recipe.query.join(IngredientsToRecipe,Recipe.recipeID == IngredientsToRecipe.recipeID).join(Ingredient,IngredientsToRecipe.siin == Ingredient.siin).add_columns(calculatedPrice).group_by(Recipe.recipeID).filter(Recipe.recipeID == self.recipeID).first()
    return recipeIngredientJoin.price

The problem is, this is setting all the price columns to the first value. Using .first to .all returns a list and that's inconvenient. Any tips or ideas?

Answer 1

Your first attempt at a hybrid property is a bit too hybrid; it performs a query, but returns an SQL expression object, the aggregate function expression, which is not usable in a WHERE clause. You should separate the two:

class Recipe(db.Model):

    # The relationship makes defining the hybrid property easier on the
    # Python side.
    ingredients = db.relationship('Ingredient', secondary='ingredients_to_recipe')

    @hybrid_property
    def calc_totalPrice(self):
        # Take a simple sum over ingredient prices, using the
        # relationship. This is meant to take place in Python.
        return sum(i.price for i in self.ingredients)

    @calc_totalPrice.expression
    def calc_totalPrice(cls):
        # Create a correlated subquery, usable in WHERE clauses, SELECT lists etc.
        # In other words, this is for SQL.
        return db.session.query(func.sum(Ingredient.price)).\
            join(IngredientsToRecipe).\
            filter_by(recipeID=cls.recipeID).\
            label('price')

A hybrid property such as this is convenient for checking the total price of a single or a few Recipe instances. The correlated scalar subquery might also perform just fine even for a larger query, given suitable indexes – looking up IngredientsToRecipe by recipeID should be made easy, for example. But it is not your only option. If you have a need for a more specific query, by all means use such. The beauty of SQLAlchemy is that it allows using the features of a relational database side by side with the ORM:

price = func.sum(Ingredient.price).label('price')

# Note that using `User` as the name of an attribute is a bit confusing.
# Doing `group_by(Recipe.recipeID)` instead of `group_by(Recipe)` is ok,
# at least in databases that recognize that the other non-aggregate columns
# have a functional dependency to the grouping column, or in other words
# `Recipe.recipeID` defines the rest. Be wary about databases that allow
# non-aggregate columns in a group even without such a dependency. You
# will probably get indeterminate results.
cheap_recipes = db.session.query(Recipe, price).\
    filter_by(User=some_user).\
    join(Recipe.ingredients).\
    group_by(Recipe.recipeID).\
    having(price <= 5.0).\
    all()