How multiply and sum two columns in different tables in django-CodePudding

I have an application for calculating diets based on the nutrients of each meal. In admin of this application I want to price of each meal to the Meal table, which I have managed to do by calculating the price when displaying it in admin:

# admin.py

class AdminMeal(admin.ModelAdmin):
    list_display = ['name', 'meal_type_names', 'price']

    @admin.display(description='Price')
    def price(self, obj):
        unit_prices = np.asarray(obj.mealingredient_set.order_by('id').values_list('ingredient__unit_price'))
        amounts = np.asarray(obj.mealingredient_set.order_by('id').values_list('amount'))
        to_return = float(np.matmul(np.transpose(unit_prices), amounts) / 1000)
        return mark_safe(to_return)

Now my main question: I need to allow ordering of Meal table based on Price which I don't know how. based on my search it seems I should use annotate instead of my current way of calculating the price to be able to sort my table, I found a solution in here

# admin.py

class AdminMeal(admin.ModelAdmin):
    list_display = ['name', 'meal_type_names', 'price']

    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        queryset = queryset.annotate(_price=Sum('mealingredient__amount * mealingredient__ingredient__unit_price'))

But sadly it throws this error (I think it's because i'm trying to SUM over different tables): Unsupported lookup 'amount * mealingredient' for AutoField or join on the field not permitted. Any help is appreciated, forgive me if I have missed something obvious, I'm a beginner in django.

Some of the relevant Models for Meal table:

# models.py

class Meal(models.Model):
    id = models.AutoField(primary_key=True)
    meal_type = models.ForeignKey(MealType, on_delete=models.CASCADE, null=True, blank=True, related_name='meal_type')
    meal_types = models.ManyToManyField(MealType, blank=True)
    name = models.CharField(max_length=255, null=True, blank=True)


class MealIngredient(models.Model):
    id = models.AutoField(primary_key=True)
    meal = models.ForeignKey(Meal, on_delete=models.CASCADE, null=True, blank=True)
    user_meal = models.ForeignKey(UserMeal, on_delete=models.CASCADE, null=True, blank=True)
    ingredient = models.ForeignKey(Ingredient, on_delete=models.SET_NULL, null=True, blank=True)
    amount = models.DecimalField(max_digits=14, decimal_places=4, default=0)


class Ingredient(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=255, null=True, blank=True)
    state = models.CharField(max_length=255, null=True, blank=True)
    unit_price = models.IntegerField(null=True, blank=True)

CodePudding user response：

You need to use ExpressionWrapper:

    from django.db.models import DecimalField, ExpressionWrapper, F, Sum

    queryset = queryset.annotate(
        _price=Sum(
             ExpressionWrapper(
                  F('mealingredient__amount') * 
                  F('mealingredient__ingredient__unit_price'),
                  output_field=DecimalField()
             )
        )
    )