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 FruitBasketGROUP BY fruit  ORDER BY id DESC

so this query doesn't magic...

SELECT * FROM (SELECT id, fruit, count FROM FruitBasketORDER BY id DESC) tGROUP 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 valuesfruits = [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.