CODEWITHSUNDEEP
pythonsqldjango
PANDA Stack
PANDA Stack

Reputation: 1363

Django ORM: Get latest record for distinct field

I'm having loads of trouble translating some SQL into Django.

Imagine we have some cars, each with a unique VIN, and we record the dates that they are in the shop with some other data. (Please ignore the reason one might structure the data this way. It's specifically for this question. :-) )

class ShopVisit(models.Model):
    vin = models.CharField(...)
    date_in_shop = models.DateField(...)
    mileage = models.DecimalField(...)
    boolfield = models.BooleanField(...)

We want a single query to return a Queryset with the most recent record for each vin and update it!

special_vins = [...]

# Doesn't work
ShopVisit.objects.filter(vin__in=special_vins).annotate(max_date=Max('date_in_shop').filter(date_in_shop=F('max_date')).update(boolfield=True)

# Distinct doesn't work with update
ShopVisit.objects.filter(vin__in=special_vins).order_by('vin', '-date_in_shop).distinct('vin').update(boolfield=True)

Yes, I could iterate over a queryset. But that's not very efficient and it takes a long time when I'm dealing with around 2M records. The SQL that could do this is below (I think!):

SELECT *
FROM cars
INNER JOIN (
   SELECT MAX(dateInShop) as maxtime, vin
   FROM cars
   GROUP BY vin
) AS latest_record ON (cars.dateInShop= maxtime)
   AND (latest_record.vin = cars.vin)

So how can I make this happen with Django?

Upvotes: 1

Views: 1916

Answers (2)

Shane
Shane

Reputation: 653

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)

Upvotes: 2

Jayground
Jayground

Reputation: 1877

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

Upvotes: 0

Related Questions