[Answered ]-Django ORM: Get latest record for distinct field

by

2👍

✅

This is somewhat untested, and relies on Django 1.11 for Subqueries, but perhaps something like:

latest_visits = Subquery(ShopVisit.objects.filter(id=OuterRef('id')).order_by('-date_in_shop').values('id')[:1])

ShopVisit.objects.filter(id__in=latest_visits)

I had a similar model, so went to test it but got an error of:
“This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery”

The SQL it generated looked reasonably like what you want, so I think the idea is sound. If you use PostGres, perhaps it has support for that type of subquery.

Here’s the SQL it produced (trimmed up a bit and replaced actual names with fake ones):

SELECT `mymodel_activity`.* FROM `mymodel_activity` WHERE `mymodel_activity`.`id` IN (SELECT U0.`id` FROM `mymodel_activity` U0 WHERE U0.`id` = (`mymodel_activity`.`id`) ORDER BY U0.`date_in_shop` DESC LIMIT 1)
👤Shane

0👍

I wonder if you found the solution yourself.

I could come up with only raw query string. Django Raw SQL query Manual

UPDATE "yourapplabel_shopvisit" 
SET boolfield = True WHERE date_in_shop 
IN (SELECT MAX(date_in_shop) FROM "yourapplabel_shopvisit" GROUP BY vin);
👤Jayground

Leave a comment