CODEWITHSUNDEEP
djangomodelfilterunique
Scott
Scott

Reputation: 3344

Query all rows and return most recent of each duplicate

I have a model that has an id that isn't unique. Each model also has a date. I would like to return all results but only the most recent of each row that shares ids. The model looks something like this:

class MyModel(models.Model):
    my_id = models.PositiveIntegerField()
    date  = models.DateTimeField()
    title = models.CharField(max_length=36)


## Add some entries
m1 = MyModel(my_id=1, date=yesterday, title='stop')
m1.save()

m2 = MyModel(my_id=1, date=today, title='go')
m2.save()

m3 = MyModel(my_id=2, date=today, title='hello')
m3.save()

Now try to retrieve these results:

MyModel.objects.all()... # then limit duplicate my_id's by most recent

Results should be only m2 and m3

Upvotes: 4

Views: 16341

Answers (3)

Greg Detre
Greg Detre

Reputation: 653

As Ned says, I don't know of a way to do this with the ORM. But you might be able to use the db to restrict the amount of work you have to do in the for loop in python.

The idea is to use Django's annotate (which is basically running group_by) to find all the instances that have more than one row with the same my_id and process them as Ned suggests. Then for the remainder (which have no duplicates), you can just grab the individual rows.

from django.db.models import Count, Q
annotated_qs = MyModel.objects.annotate(num_my_ids=Count('my_id')).order_by('-date')
dupes = annotated_qs.filter(num_my_ids__gt=1)
uniques = annotated_qs.filter(num_my_ids__lte=1)
for dupe in dupes:
   ... # just keep the most recent, as Ned describes
keep_ids = [keep.id for keep in keeps]
latests = MyModel.objects.filter(Q(id__in=keep_ids) | Q(id__in=uniques))

If you only have a small number of dupes, this will mean that your for loop is much shorter, at the expense of an extra query (to get the dupes).

Upvotes: -1

Ned Batchelder
Ned Batchelder

Reputation: 375594

You won't be able to do this with just the ORM, you'll need to get all the records, and then discard the duplicates in Python.

For example: 

objs = MyModel.objects.all().order_by("-date")
seen = set()
keep = []
for o in objs:
    if o.id not in seen:
        keep.append(o)
        seen.add(o.id)

Here's some custom SQL that can get what you want from the database:

select * from mymodel where (id, date) in (select id, max(date) from mymodel group by id)

You should be able to adapt this to use in the ORM.

Upvotes: 11

Sarkis Varozian
Sarkis Varozian

Reputation: 161

You should also look into abstracting the logic above into a manager:

http://docs.djangoproject.com/en/dev/topics/db/managers/

That way you can call something like MyModel.objects.no_dupes() where you would define no_dupes() in a manager and do the logic Ned laid out in there.

Your models.py would now look like this:

class MyModelManager(models.Manager):
    def no_dupes:
        objs = MyModel.objects.all().order_by("-date")
        seen = set()
        keep = []
        for o in objs:
            if o.id not in seen:
                keep.append(o)
                seen.add(o.id)
        return keep

class MyModel(models.Model):
    my_id = models.PositiveIntegerField()
    date  = models.DateTimeField()
    title = models.CharField(max_length=36)
    objects = MyModelManager()

With the above code in place, you can call: MyModel.objects.no_dupes(), this should give your desired result. Looks like you can even override the all() function as well if you would want that instead:

http://docs.djangoproject.com/en/1.2/topics/db/managers/#modifying-initial-manager-querysets

I find the manager to be a better solution in case you will need to use this in more than one view across the project, this way you don't have to rewrite the code X number of times.

Upvotes: 1

Related Questions