I calculated one variable using the annotate and now i want to compare it with the existing field. So how can I do that?

models.py
class Book(models.Model):
    name = models.CharField(max_length=50)
    price = models.IntegerField()

query is look like this
aggregate_query = {
     "max-price": (
          aggregate(Avg('price'))
     )
}
input_queryset = Books.objects.annotate(**aggregate_query)

So now i want to improve the query to filter the data of those books whose price is below the max and more than the average value.

CodePudding user response：

from django.db.models import Max

max_price = Book.objects.all().aggregate(Max('price'))

You get the max price.

from django.db.models import Avg
average = Book.objects.all().aggregate(Avg('price'))

Now, you have the max price and average.

Book.objects.filter(price__lte = max_price,price__gte=average) 

or

from django.db.models import Q
Book.objects.filter(Q(price__lte=max_price),Q(price__gte=average))

For reference use this link

Thank You !!!!

CodePudding user response：

We do not have to check the maximum since, well, the maximum is the book with the largest value, so all items have a price which is less than or equal to the average.

You can fetch the average in a single query with:

from django.db.models import Avg

avg = Books.objects.aggregate(
    avg=Avg('price')
)['avg']

and then filter with:

Books.objects.filter(price__gte=avg)

If you determine the maximum based on a filter condition, we can fetch bot the maximum and average with the same query:

from django.db.models import Avg, Max

aggs = Books.objects.filter(
    # …
).aggregate(
    avg=Avg('price'),
    max=Max('price')
)

Books.objects.filter(price__range=(aggs['avg'], aggs['max']))