Django: filter by last for a single column

Question

Given this FruitBasket model,

class FruitBasket (Model):
    fruit = CharField (max_length = 128)
    count = PositiveIntegerField ()

And this sample data

id fruit count
----- ---------- -----
0 apple 10
1 banana 20
2 apple 5
3 banana 30

I need a django request that returns the following elements:

[(2, apple, 5), (3, banana, 30)]

Essentially, grab the “last” row for each fruit (I simplified the timestamp for rowid in this example.)

Answer 1

https://docs.djangoproject.com/en/dev/ref/models/querysets/#distinct

q = FruitBasket.objects.distinct('fruit')

postgres.

PostgreSQL (* ) , DISTINCT . SELECT DISTINCT ON SQL. . Different() , . Different() , .

, order_by, :

q = FruitBasket.objects.distinct('fruit').order_by('fruit')

, order_by() QuerySet, order_by() Different(), .

, SELECT DISTINCT ON (a) . , .

values , : /order_by, .

q = (
    FruitBasket.objects
    .values('id', 'fruit', 'count')
    .distinct('fruit').order_by('-id')
)

, ORM

SELECT id, fruit, count 
FROM FruitBasket
GROUP BY fruit  
ORDER BY id DESC

...

SELECT * FROM (SELECT id, fruit, count 
FROM FruitBasket
ORDER BY id DESC) t
GROUP BY t.fruit

, .

:

q = FruitBasket.objects.raw("""\
    SELECT * FROM 
    (
        SELECT id, fruit, count 
        FROM FruitBasket 
        ORDER BY id DESC
    ) t
    GROUP BY t.fruit
""")

Answer 2

:

FruitBasket.objects.order_by('fruit', '-count').distinct('fruit')

Django 2.1

Answer 3

, () , ( , ):

available_fruits = ['banana', 'apple'] # can be also an extra query to extract distinct values
fruits = [FruitBasket.objects.filter(fruit=x).latest('id') for x in available_fruits ]

4 , 4 .