apply filter only on one annotate columns without effect on other annotate columns?

Question

i grouped by a value then sum of a field , but now i want to only summation happen if a column inside annotate greater that or equal to 1 !

my models.py

class MyModel(models.Model):
    name = models.ForeignKey(Product, on_delete=models.CASCADE)
    order = models.IntegerField()
    price = models.IntegerField()

class Prodcut(models.Model):
    name = models.CharField(max_lenth=20)
    cost = models.IntegerField()
    price = models.IntegerField()

this is my query

MyModel.objects.values('name__name').annotate(
            income=(
                    Sum(((F('price')*F('order')) - (
                        (F('name__price')+F('name__cost'))*F('order'))
                            ),output_field=IntegerField())),quantity=(
                                        Sum(F('order'))
                                    ))

sometimes happen income smaller than 0 (negative values) , i dont want summation of those products which their incomes smaller than 0 , i want to use something like this inside the annotate or Sum income__gte=1 !? ,

i have some more fields i dont want to use .filter at the end of )annotate

is it possible only add to income column if the new income greater than or equal to 1?

Does this answer your question? [Filtering only on Annotations in Django](https://stackoverflow.com/questions/2096580/filtering-only-on-annotations-in-django) — Lord Elrond, Jun 26 '20 at 19:42
Surely when a product's price + cost is greater than the charged price a negative income is valid? — Iain Shelvington, Jun 26 '20 at 19:48
@ReinstateMonica i want to filter by the income columns not others , it doesnt work in my case — art_cs, Jun 26 '20 at 19:49
@IainShelvington yes negative result happens sometimes (when i sell something which have no any income , its loss — art_cs, Jun 26 '20 at 19:54
@IainShelvington i dont want to count negative results to my income , without using .filter at the end , because it affect on my other annotate columns — art_cs, Jun 26 '20 at 19:55
Just to clarify. You want a queryset of all `MyModel` with each result annotated with `income` and income is either greater than 0 or 0? — Iain Shelvington, Jun 26 '20 at 20:26
imagine , i make a post on MyModel(name=mouse ,order=2,price=20) and in my Product model i have these information for mouse product Product(name=mouse,cost=4,price=10) , when i calculate to find income for this product : (2 *20) - ((4+10)*2) => 40 - 28 = 12 , but sometimes happen the result will be a negative price when (2*10) - ((4+10)*2) => 20 - 28 = -8 — art_cs, Jun 26 '20 at 20:34

Iain Shelvington · Accepted Answer · 2020-06-26T20:40:09.030

2

You can add a second annotation that filters the first annotation. In this case you can use Greatest to return 0 if the actual income is negative

from django.db.models.functions import Greatest
MyModel.objects.annotate(
    actual_income=F('price') * F('order') - (F('name__price') + F('name__cost')) * F('order'),
).annotate(
    income=Greatest(F('actual_income'), 0)
)

I often find it make things clearer to use multiple named annotations

MyModel.objects.annotate(
    total_charged=F('price') * F('order'),
    total_paid=(F('name__price') + F('name__cost')) * F('order')
).annotate(
    revenue=F('total_charged') - F('total_paid')
).annotate(
    income=Greatest(F('revenue'), 0)
)

edited Jun 26 '20 at 20:40

answered Jun 26 '20 at 20:32

Iain Shelvington

31,030
3
31
50

1

its worked as magic , i appreciate you , saved my night – art_cs Jun 26 '20 at 20:40
is there something similar to Smallest ? i also want to calculate negative number ? – art_cs Jun 26 '20 at 20:55
2

There is `Least` https://docs.djangoproject.com/en/3.0/ref/models/database-functions/#least – Iain Shelvington Jun 26 '20 at 20:56
sorry i got a problem , it only prevent income reaching negative values and stop in 0 if a negative value appeared , and still calculate with negative values ? – art_cs Jun 26 '20 at 21:31
MyModel is only related to 1 product? What do you mean it stops? Do you have another annotation/filter after this? – Iain Shelvington Jun 26 '20 at 21:35
no i have multi products with different price and costs,whenever income column reach negatives it only display 0 in background for example its -5 showing 0 till a new income with greater than 5 then (if new income =6) income will be 1, yes i have multiple annotation , i've updated with the other annotation (quantity) – art_cs Jun 26 '20 at 21:40
it should be something before Sum function happened – art_cs Jun 26 '20 at 21:45
should i give up on it to solve? or we can do something else please ? – art_cs Jun 26 '20 at 22:08
can we use if else in Sum? i tried but it seems if else not work if not it will solve the issue – art_cs Jun 26 '20 at 22:31
i made a new post https://stackoverflow.com/questions/62606945/django-using-if-else-or-while-else-inside-sum-function-with-annotation-cannot if could help please let me know – art_cs Jun 27 '20 at 08:18

apply filter only on one annotate columns without effect on other annotate columns?

1 Answers1