Getting most recent instance of each category in a queryset
I've got a model 'Thing' with these fields:
name: TextField
category: TextField
date: DateField
I first do an icontains query on name:
Thing.objects.filter(name__icontaints='substring')
开发者_如何学Python
My question is how to filter that result to only give the most recent item in each category.
If I get three results with a category of 'widget' I only want one widget in the result set - the most recent one.
My naive solution would be to iterate through the queryset, keep a record of most recent dates and construct a new result set. Is there a better way? One that preferably works at the database level.
Unfortunately there is no way to do this reliably across all databases since the queries vary quite a bit.
With MySQL you could do something like this:
SELECT
name,
category,
date
FROM
thing
WHERE
name LIKE '%substring%'
GROUP BY date
With PostgreSQL it would be something like this:
SELECT
DISTINCT ON (category)
name,
category,
date
FROM
thing
WHERE
name LIKE '%substring%'
ORDER BY date DESC
If you are using PostgreSQL, than you can use this patch to get DISTINCT ON
support: http://code.djangoproject.com/ticket/6422
精彩评论