CODEWITHSUNDEEP
djangodjango-models
Andy Baker
Andy Baker

Reputation: 21587

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

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.

Upvotes: 1

Views: 195

Answers (1)

Wolph
Wolph

Reputation: 80061

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

Upvotes: 2

Related Questions