Select rows with multiple duplicate field values

Question

I want to select rows in Django which have multiple rows with duplicated values.

For example I have a model which looks like this

class User:
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    ...

and a database looking like this

first_name | last_name | ...
---------- | --------- | ...
Paul       | Adams     | ...
John       | Smith     | ...
Tom        | Mueller   | ...
John       | Smith     | ...
John       | Adams     | ...
Paul       | Adams     | ...

and I want to get the first_name and last_name values which exist multiple times in the database. The other values must not be equal of the rows. In this example I want to get "John Smith" and "Paul Adams".

@Brambor No it isn't. I found this question already, but it only works if the value of one field is duplicated, not of multiple fields. — leona
– leona, Commented Nov 26, 2020 at 10:24
Can't you just use the answer twice to filter by two columns? — Brambor
– Brambor, Commented Nov 26, 2020 at 10:26
I tried it and it didn't work, maybe I did something wrong. I can try again later. — leona
– leona, Commented Nov 26, 2020 at 10:28
try doing something like .values("first_field", "second_field") — Alvi15
– Alvi15, Commented Nov 26, 2020 at 10:29

Davit Tovmasyan · Accepted Answer · 2020-11-26 10:39:19Z

3

First of all, you need to concat fields.

from django.db.models import Count, Value
from django.db.models.functions import Concat

queryset = User.objects.annotate(full_name=Concat('first_name', Value(' '), 'last_name', output_field=CharField())

then you need to group and calculate the count for each unique full_name

queryset = queryset.values('full_name').annotate(full_name_count=Count('full_name'))

and just filter those with count bigger than one.

queryset = queryset.filter(full_name_count__gt=1)

answered Nov 26, 2020 at 10:39

Davit Tovmasyan

3,6382 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yogendra maarisetty · Accepted Answer · 2020-11-26 10:35:08Z

2

You can do this in views.py or in your django shell using py manage.py shell

from django.db.models import Count
#import your User model 

users_output = User.objects.values('first_name','last_name').annotate(first_name_count=Count('first_name'),last_name_count = Count('last_name')).filter(first_name_count__gt=1,last_name_count__gt=1)

the above query will return a query set something like this

<QuerySet
 [{'first_name': 'John', 'last_name': 'Smith', 'first_name_count': 2, 'last_name_count': 2},
 {'first_name': 'Paul', 'last_name': 'Adams', 'first_name_count': 2, 'last_name_count': 2}]
>

answered Nov 26, 2020 at 10:35

yogendra maarisetty

3803 silver badges16 bronze badges

Collectives™ on Stack Overflow

Select rows with multiple duplicate field values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related