CODEWITHSUNDEEP
pythondjango
Brandon Bertelsen
Brandon Bertelsen

Reputation: 44648

Find most recent distinct queryset by field with MySQL Backend

Let's say I have a model:

class Calls(models.Model): 
    callid = models.AutoField(primary_key=True)
    altid = models.BigIntegerField()
    calldate = models.DateField(auto_now=True, verbose_name='Call Date')
    followupdate = models.DateField(blank=True,null = True, verbose_name='Follow-up Date')

What I would like to do is this: 

Calls.objects.order_by("followupdate").distinct('altid')

But since I'm using MySQL, it seems that I can't use distinct on a particular field. As per the first note in the docs on distinct. When I try to run I receive an error. NotImplementedError: DISTINCT ON fields is not supported by this database

Below is a minimal example of my table, and how I would like to query to act. Respectively, the comma seperated values represent: callid,altid,calldate, and followupdate.

What I have:

1,1,yesterday,thursday
2,2,yesterday,next wednesday
3,1,thursday,next thursday

What I want: 

2,2,yesterday,next wednesday
3,1,thursday,next thursday

Note: I cannot change to PostgreSQL

How can I accomplish this?

Upvotes: 0

Views: 1109

Answers (1)

Colleen
Colleen

Reputation: 25489

perhaps not the most efficient solution (my gut tells me something with sets could be marginally better, if sets have some kind of distinct lambda function or something...), but would easily work to do:

results = Calls.objects.order_by("followupdate")
newresults = []

seen_altid = []

for result in results:
    if result.altid not in seen_altids:
        seen_altids.append(result.altid)
        newresults.append(result)

btw, though, sounds like you want to order by -followupdate if you use this solution so that you'll get 3,1 and not 1,1

EDIT: jk, http://www.peterbe.com/plog/uniqifiers-benchmark seems to back me up on this solution. With the slight modification of seen being a dict, presumably for O(1) lookups. Which is cool if you need it (though unless your result set size is enormous it's really probably unnecessary)

Upvotes: 1

Related Questions