How to find duplicate records based on certain fields in Django

Question

I need to create new field in the queryset that flags if a record is a duplicate or not. I consider the concatenated values of 2 fields as an identifier. If they are seen more that once in the query set (the field that is concatenated), then the record is considered a duplicate.

First, on my query set, I create another field from the existing 2 fields which is case number and hearing date. and their output field name is dupe_id

    qs = file.objects.annotate(
            dupe_id=Concat(
                        F('case_no')
                        , F('hearing_date')
                        , output_field=CharField()
            )
        )

then I test this dupe_id field for count. If the count is more than 1, then it is considered as duplicate

    dupes = qs.values('dupe_id').annotate(dupe_count=Count('dupe_id')).filter(dupe_count__gt=1)

at this point I now have another query set the contains the duplicate values from the original query set. Here are the records seen from the dupe object which is of type queryset. It also states the number of instances the value was found

Now this is where I'm having a bit of difficulty. What I'm thinking is that I will do an annotation on my main query set and I will use the dupes query set to help in identifying the records that need to be tagged as duplicate.

I tried this:

    qs = qs.annotate(
            dupe_id2 = Value(('duplicate' if dupes.filter(dupe_id__exact=Concat(F('case_no'), F('hearing_date')))[0] else '--'), output_field=CharField())
        )

This is just a simple test that says if the concatenated values are seen in the dupes query set, then the field will be tagged as duplicate, if not then '--'.

But it does not seem to work as expected. All the records are being tagged as duplicate even though I have 1 record that should not be tagged as duplicate.

Also I checked using conditional expressions but I won't be able to use the dupes query set I created.

If there is a more robust way of tagging records in a query set as duplicate, please let me know.

user_232649 · Answer

One of the ways to work on duplicates is to use the algorithm of:
GroupBy in SQL > Find Duplicates > loop over duplicates

from django.db.models import Max, Count

# Getting duplicate files based on case_no and hearing_date
files = File.objects.values('case_no', 'hearing_date') \
    .annotate(records=Count('case_no')) \
    .filter(records__gt=1)

# Check the generated group by query
print files.query

# Then do operations on duplicates
for file in files:
    File.objects.filter(
        case_no=file['case_no'],
        hearing_date=file['hearing_date']
    )[1:].update(duplicate=True)

user_1000281 · Answer

It turns out it is not possible to perform conditional operations on the annotate function of a query set.

What I did was to override the get_context_data function, then get the duplicate keys. The returned object was a queryset so I took all the IDs and then put them in a list, then stored them to context which was made available in the template view.

This is what my get_context_data function looks like, if it could be further improved please let me know.

def get_context_data(self, **kwargs):
    ctx = super(fileList, self).get_context_data(**kwargs)

    qs = file.objects.annotate(
            dupe_id=Concat(
                        F('case_no')
                        , F('hearing_date')
                        , output_field=CharField()
            )
        )

    dupes = qs.values('dupe_id').annotate(dupe_count=Count('dupe_id')).filter(dupe_count__gt=1)

    dupe_keys = []
    for dupe in dupes:
        dupe_keys.append(dupe['dupe_id'])

    ctx['dupe_keys'] = dupe_keys

    return ctx

Now on the template view, on the for loop of the queryset, I just created another column which checks if the id in the queryset is visible in the list of duplicates, then the record will have a special tagging of duplicate or the cell will be highlighted to something visible to the user.

{% if object.dupe_id in dupe_keys %} duplicate {% else %} not duplicate {% endif %}

How to find duplicate records based on certain fields in Django

2 Answers