Changes between Version 1 and Version 3 of Ticket #30188


Ignore:
Timestamp:
Feb 18, 2019, 5:09:19 AM (6 years ago)
Author:
Lukas Klement
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #30188 – Description

    v1 v3  
    11Aggregating annotations works for simple Sum, Count, etc. operations, but fails when the Sum() contains a Case() When() operation.
    22
    3 Example:
    4 Objective: sum the number of None values for the annotated field field_name across a queryset.
     3To reproduce the issue, a simplified scenario ():
     4Suppose you have recipes (e.g. Pasta dough, Tomato Sauce, etc.), which are linked to dishes (e.g. Pasta with tomato sauce) and ingredients (e.g. Flour, Water, Tomatoes). Ingredients are shared across all users, but each user can add a different purchase price to an ingredient (-> cost). We want to sum up the cost of a dish (i.e. summing up the cost of all ingredients across all recipes of a dish).
     5
     6**models.py**
    57{{{
    6 field_name = <name of annotated field>
    7 missing_data_agg=Sum(
    8     Case(
    9             When(**{field_name: None}, then=Value(1)),
    10             default=Value(0),
    11             output_field=IntegerField()
     8class Ingredient(models.Model):
     9    pass
     10
     11class IngredientDetail(models.Model):
     12    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE, related_name='ingredient_details')
     13    user = models.ForeignKey(User, limit_choices_to={'role': 0}, on_delete=models.CASCADE)
     14    cost = models.DecimalField(blank=True, null=True, decimal_places=6, max_digits=13)
     15    cost_unit = models.CharField(choices=UNITS, max_length=15, default='kg')
     16
     17
     18class Recipe(models.Model):
     19    dishes = models.ManyToManyField(Dish, related_name='dishes_recipes', through='RecipeDish')
     20    ingredients = models.ManyToManyField(Ingredient, related_name='ingredients_recipes', through='RecipeIngredient')
     21
     22class RecipeDish(models.Model):
     23    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, related_name='recipedish_recipe')
     24    dish = models.ForeignKey(Dish, on_delete=models.CASCADE, related_name='recipedish_dish')
     25
     26
     27class RecipeIngredient(models.Model):
     28    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, related_name='recipeingredient_recipe')
     29    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE, related_name='recipeingredient_ingredient')
     30    quantity = models.DecimalField(blank=True, null=True, decimal_places=2, max_digits=6)
     31}}}
     32
     33
     34
     35Different users can attach different IngredientDetail objects to a shared Ingredient objects (one-to-many relationship). The objective is to calculate how many ingredients added to a recipe (many-to-many relationship through RecipeIngredient) don't have a cost value set. To achieve this without using .extra(), we annotate the appropriate cost field to IngredientNutrition, then sum up the missing values using a Sum(), Case() and When() aggregation.
     36
     37{{{
     38def aggregate_nutrition_data(recipes, user):
     39    annotations['cost_value'] = Subquery(
     40        IngredientDetail.objects.filter(Q(ingredient_id=OuterRef('ingredient_id')) & Q(user=user)).values('cost')[:1]
    1241    )
    13 )
     42    ingredients = RecipeIngredient.objects.filter(recipe__in=recipes).annotate(**annotations)
     43
     44    aggregators['cost_missing'] = Coalesce(Sum(
     45        Case(
     46           When(**{'cost_value': None}, then=Value(1)),
     47           default=Value(0),
     48           output_field=IntegerField()
     49       )
     50    ), Value(0))
     51
     52    data = ingredients.aggregate(**aggregators)
     53    return data
    1454}}}
    15 Expected behaviour: calling queryset.aggregate(missing_data=missing_data_agg) returns a dict {'missing_data': <value>}
    16 Actual behaviour: AssertionError No exception message supplied is returned.
    1755
    18 When field_name is set to a field that exists on the model of the queryset, the aggregation works. When field_name is set to a field that is neither in the model of the queryset, nor has it been annotated to the queryset, the exception returned is as expected: Cannot resolve keyword <name of the non existing field> into field . Choices are: <list of model and annotated fields. In this list, the field_name that has been annotated to the queryset is shown -> hence it should work, hence I assume this is a Django bug.
     56Expected behaviour: a dictionary is returned: {'cost_missing': <value>}, containing as a value the number of missing cost values.Actual behaviour: AssertionError No exception message supplied is returned on the line ''data = ingredients.aggregate(**aggregators)'' (see abbreviated stacktrace below).
    1957
    20 Stacktrace:
     58When the aggregated field name is set to a field that exists on the model of the queryset (i.e. is not annotated), the aggregation works. For example: instead of using cost_value=None in the When() of the aggregators, using quantity=None works.
     59Similarly, doing an aggregation over an annotated field without using Case() and When() works. For example:
     60{{{
     61    aggregators['cost'] = Coalesce(
     62        Sum(
     63            F('cost_value') * F('quantity') / F('cost_quantity')
     64            output_field=DecimalField()
     65        ), Value(0)
     66}}}
    2167
     68
     69----
     70
     71
     72**Stacktrace:**
    2273{{{
    2374<Line with the .aggregate(...)>
Back to Top