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 ?

score 20 · Accepted Answer · edited Apr 15 '21 at 17:47

20

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

edited Apr 15 '21 at 17:47

phoenix

7,988
6
39
45

answered Oct 30 '13 at 09:21

Prashant Gaur

9,540
10
49
71

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 ? – Shiva Krishna Bavandla Oct 30 '13 at 09:35
yep sure, i hope u found my above comment/doubt ? – Shiva Krishna Bavandla Oct 30 '13 at 09:39
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 – Prashant Gaur Oct 30 '13 at 09:42
The annotate here does nothing, this is equivalent to `Choclate.objects.filter().order_by('price').values_list('name')[0]` – Luciano May 29 '23 at 23:35

score 4 · Answer 2 · answered Oct 30 '13 at 09:16

4

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

answered Oct 30 '13 at 09:16

Maciej Gol

15,394
4
33
51

Jcc.Sanabria · Answer 3 · 2019-10-23T16:11:15.197

4

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

edited Oct 23 '19 at 16:11

answered Oct 23 '19 at 15:35

Jcc.Sanabria

629
1
12
22

score 3 · Answer 4 · answered Feb 24 '22 at 09:12

3

Usage of what @Maciej Gol said:

from django.db.models import Min    

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

answered Feb 24 '22 at 09:12

Abra Cadabra

141
6

This does give OP the `lowest price`, but does not give him the `field name`. – Jakub Holan Jul 06 '23 at 18:25

score 0 · Answer 5 · answered Jul 06 '23 at 18:44

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

Get minimum value field name using aggregation in django

5 Answers5

Code

Explanation

Linked