[Django]-Remove duplicates in a Django query

For checking duplicate you can do a GROUP_BY and HAVING in Django as below. We are using Django annotations here.

from django.db.models import Count
from app.models import Email

duplicate_emails = Email.objects.values('email').annotate(email_count=Count('email')).filter(email_count__gt=1)

Now looping through the above data and deleting all other emails except the first one (depends on requirement or whatever).

for data in duplicates_emails:
    email = data['email']
    Email.objects.filter(email=email).order_by('pk')[1:].delete()

You can chain .distinct() on the end of your queryset to filter duplicates. Check out: http://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.distinct

You may be able to use the distinct() function, depending on your model. If you only want to retrieve a single field form the model, you could do something like:

email_list = Emails.objects.values_list('email').order_by('email').distinct()

which should give you an ordered list of emails.

You can also use set()

email_list = set(Emails.objects.values_list('email', flat=True))

Use, self queryset.annotate()!

from django.db.models import Subquery, OuterRef

email_list = Emails.objects.filter(
    pk__in = Emails.objects.values('emails').distinct().annotate(
        pk = Subquery(
        Emails.objects.filter(
          emails= OuterRef("emails")
        )
        .order_by("pk")
        .values("pk")[:1])
    )
    .values_list("pk", flat=True)
)

This queryset goes to make this query.

 SELECT `email`.`id`,
        `email`.`title`,
        `email`.`body`,
       ...
       ...
  FROM `email`
 WHERE `email`.`id` IN (
        SELECT DISTINCT (
                SELECT U0.`id`
                  FROM `email` U0
                 WHERE U0.`email` = V0.`approval_status`
                 ORDER BY U0.`id` ASC
                 LIMIT 1
               ) AS `pk`
         FROM `agent` V0
 )

cheet-sheet

from django.db.models import Subquery, OuterRef

group_by_duplicate_col_queryset = Models.objects.filter(
    pk__in = Models.objects.values('duplicate_col').distinct().annotate(
        pk = Subquery(
        Models.objects.filter(
          duplicate_col= OuterRef('duplicate_col')
        )
        .order_by("pk")
        .values("pk")[:1])
    )
    .values_list("pk", flat=True)
)

I used the following to actually remove the duplicate entries from from the database, hopefully this helps someone else.

adds = Address.objects.all()
d = adds.distinct('latitude', 'longitude')
for address in adds:    
  if i not in d:
    address.delete()

if you want remove duplicacy from the queryset, for eg. let’s say you have the user model with fields like name, email and you want remove duplicate emails then, you can simply use distinct() method

User.objects.all().distinct("email")

it will return all the unique emails.

you can use this raw query : your_model.objects.raw("select * from appname_Your_model group by column_name")