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