[Fixed]-Django: filter by latest per distinct column

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
""")

You can try this:

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

In my case it worked for Django 2.1

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.

Subquery might help you out here,
an example from the docs:

>>> from django.db.models import OuterRef, Subquery
>>> newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
>>> Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))