Django: filter by latest per distinct 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 want a django query that returns the following items:

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

Essentially, grabbing the "latest" row per fruit (I've simplified the timestamp to rowid in this example.)

Answer 1

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

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

will only work if you are using postgres.

On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query. Here’s the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.

Additionally, you would have to specify an order_by and it couldn't be by the timestamp:

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

When you specify field names, you must provide an order_by() in the QuerySet, and the fields in order_by() must start with the fields in distinct(), in the same order.

For example, SELECT DISTINCT ON (a) gives you the first row for each value in column a. If you don’t specify an order, you’ll get some arbitrary row.

however, values might get you closer if you could kill the requirement: distinct/order_by having the same values in the same order.

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

realistically, sometimes its better to break out of the ORM

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

so this query doesn't magic...

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

this ones better but kinda ugly.

optimize this on your own:

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

Answer 2

You can try this:

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

In my case it worked for Django 2.1

Answer 3

As an alternative, if you have fixed (small) amount of possible distinct values, you can use several queries (not really optimal, but should work for small projects):

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 ]

In my case it was only 4 values, so I'm ok with making 4 simple and fast queries.