Get minimum value field name using aggregation in django

Question

I have a model with some fields like below

class Choclate(models.Model):
    name = models.CharField(max_length=256)
    price = models.IntegerField()

So i want to get the field name that has the lowest price value, so in order to get the lowest price value, we can do like below using Aggregations

from django.db.models import Avg, Max, Min

choclates = Choclate.objects.all()
lowest_price = choclates.aggregate(Min('price'))

So finally how to get the field name, that related to lowest price value in django ?

Beating

Answer 1

You can try below code to get exact thing you want

>>> from django.db.models import Min
>>> Choclate.objects.filter().values_list('name').annotate(Min('price')).order_by('price')[0]
(u'First1', 10)
>>>

First1 is the field name having price = 10 which is lowest value.

Documentation Link

Dhaulagiri

k thanks thats what i want, also is there any way to find that if all the price values are equal, i mean if there are 4 choclate records, i need to check whether all the four records have same price value , is it possible ? – Beating

yep sure, i hope u found my above comment/doubt ? – Beating

if i will try i will put some if else condition after fetching all 4 records from db .i did not find and specific query for this – Dhaulagiri

The annotate here does nothing, this is equivalent to Choclate.objects.filter().order_by('price').values_list('name')[0] – Incorrupt

This query actually have a bad performance on large tables. – Koonce

Answer 2

For Min and Max you may order your values (QuerySet result) and grab the first() and last():

chocolate_list = Chocolate.objects.values_list('name', 'price')
min = chocolate_list.order_by('price').first()
max = chocolate_list.order_by('price').last()

PS: Remove the filter() if you are not assigning nothing. With values_list() you are implicitly instancing the QuerySet class

Bongbongo

Answer 3

If you pass the Min as positional argument, then the field's name is price__min. Otherwise, if you pass it as keyword argument, i.e. aggregate(my_min=Min('price')), then it will be available with the same name as the argument, in this case my_min. Docs

Food

Answer 4

Usage of what @Maciej Gol said:

from django.db.models import Min    

lowest_price = Chocolate.objects.values('price').aggregate(Min('price'))['price__min']

Koonce

This does give OP the lowest price, but does not give him the field name. – Vega

Answer 5

Expanding on Jcc's answer and Luciano's comment, the solution for the exact OP's question would be the following.

Code

cheapest_choclate = Choclate.objects.values_list('name', 'price').order_by('price')[0]
lowest_price = cheapest_choclate['price']
cheapest_choclate_name = cheapest_choclate['name']

Explanation

Using Choclate.objects, you obtain all objects of class Choclate from the DB.

Using .values_list('name', 'price'), you create a QuerySet with a list of tuples where each tuple contains the 'name' and 'price' of each object - e.g. [('Sweet', 79),('Bitter', 49), ('Spicy', 69)].

Using .order_by('price'), you order the tuples in the list using the price - e.g. [('Bitter', 49), ('Spicy', 69), ('Sweet', 79)].

Using [0], you select the first tuple from the list. This tuple contains the name and price of the cheapest choclate. You could also use .first() as suggested by Jcc. However, I like the [0] approach more as it can easily be changed to something else if needed - e.g. "second cheapest" choclate could be obtained using [1].

Lastly, you can obtain the price and name of the cheapest choclate from the first tuple using cheapest_choclate['price'] and cheapest_choclate['name'].

Vega