Django: list all objects with non unique field

Question

If I have the following Model:

class Person(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

how do I list all objects that have a non unique last_name field? For example, if there are 20 objects that all have the last_name 'Smith', they would be listed. However, if only one object had the last_name 'Euler' it would not be included in the list.

I think, this is not task for Django ORM. Try raw sql and check this question: http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql — Denis Kabalkin, Dec 31 '11 at 10:27

score 6 · Accepted Answer · answered Dec 31 '11 at 10:26

6

Here's one way with 2 queries:

non_unique_last_names = (Person.objects
    .values_list('last_name', flat=True)
    .annotate(last_name_count=Count('last_name'))
    .filter(last_name_count__gt=1))

non_unique_person_objects = Person.objects.filter(last_name__in=non_unique_last_names)

answered Dec 31 '11 at 10:26

Yuji 'Tomita' Tomita

115,817
29
282
245

Where does the Count class come from? – serverpunk Apr 07 '15 at 18:51
@Jura `from django.db.models import Count` – Yuji 'Tomita' Tomita Apr 07 '15 at 19:37

score 5 · Answer 2 · answered Sep 13 '18 at 13:52

You will probably want to cast list to non_unique_last_names to fight OperationalError: sub-select returns 2 columns - expected 1

So, to those who will use this code, the right version is:

from django.db.models import Count
non_unique_last_names = (Person.objects
    .values_list('last_name', flat=True)
    .annotate(last_name_count=Count('last_name'))
    .filter(last_name_count__gt=1))

non_unique_person_objects = Person.objects.filter(last_name__in=list(non_unique_last_names))

Django: list all objects with non unique field

2 Answers2