开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜