CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-viewsdjango-orm
VRAJESH VAGHASIYA
VRAJESH VAGHASIYA

Reputation: 41

Compare field in the table and calculated field in django-orm

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.

Upvotes: 2

Views: 324

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476740

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']))

Upvotes: 1

Akash Nagtilak
Akash Nagtilak

Reputation: 325

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 !!!!

Upvotes: 2

Related Questions