Django Select Only Rows with Duplicate Field Values

Django select only rows with duplicate field values

Try:

from django.db.models import Count
Literal.objects.values('name')
               .annotate(Count('id')) 
               .order_by()
               .filter(id__count__gt=1)

This is as close as you can get with Django. The problem is that this will return a ValuesQuerySet with only name and count. However, you can then use this to construct a regular QuerySet by feeding it back into another query:

dupes = Literal.objects.values('name')
                       .annotate(Count('id'))
                       .order_by()
                       .filter(id__count__gt=1)
Literal.objects.filter(name__in=[item['name'] for item in dupes])

Select rows with multiple duplicate field values

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}]
>

How to find duplicate records based on certain fields in Django

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.

<td>{% if object.dupe_id in dupe_keys %} duplicate {% else %} not duplicate {% endif %}</td>

Django ORM. Select only duplicated fields from DB

You should first be aware of how count works.
The Count method will count for identical rows.
It uses all the fields available in an object to check if it is identical with fields of other rows or not.
So in current situation the count_values is resulting 1 because Count is using all fields excluding id to look for similar rows.
Count is including user,value,text,something1,something2 fields to check for similarity.

To count rows with similar fields you have to use only user,values & text field

Query:

  recs = MyTableWithValues.objects
         .values('user','values','text')
         .annotate(max_id=Max('id'),count_id=Count('user__id'))
         .annotate(count_values=Count('values'))

It will return a list of dictionary

  print(recs)

Output:

<QuerySet[{'user':1,'values':1,'text':'asdasdasdasd','max_id':3,'count_id':2,'count_values':2},{'user':2,'values':2,'text':'QWQWQWQWQWQW','max_id':2,'count_id':1,'count_values':1}]

using this queryset you can check how many times a row contains user,values & text field with same values

Django Count rows with duplicate values

Try this:

In your Views.py use this queryset:

repeated_names = Application.objects.values('school_name', 'category').annotate(Count('id')).order_by().filter(id__count__gt=0)   # <--- gt 0 will get all the objects having occurred in DB i.e is greater than 0

gt 0 will get all the objects having occurred in DB i.e is greater than 0

In your Template:

 {% for c in repeated_names %}

 <ul>

 <li>{{c.school_name}}</li>

 <li>{{c.id__count}}</li>

 <li>{{c.category}}</li>


</ul>

{% endfor %}

Django select rows with duplicate field values for specific foreign key

It sounds to me as though you want to execute a SQL query something like this:

SELECT l1.* FROM myapp_literal AS l1,
                 myapp_literal AS l2
WHERE l1.id <> l2.id
  AND l1.name = l2.name
  AND l1.concept = l2.concept
  AND l1.id NOT IN (SELECT main_name FROM myapp_concept)
GROUP BY l1.id

Well, in cases where the query is too complex to easily express in Django's query language, you can always ask Django to do a raw SQL query—and this may be one of those cases.

How to find duplicate and deactivate duplicates for user attributes

There a great answer for multiple duplicate fields queryset from this answer as i don't want to take the credit and also don't want to reinvent the wheel, so i will suggest that answer

For your case it should be:

from django.db.models import Max, Count

duplicate_date_class = DateClass.objects.values('user_id', 'sp_date') \
    .annotate(records=Count('user_id')) \
    .filter(records__gt=1)

# Then do operations on duplicates
for date_class in duplicate_date_class:
    DateClass.objects.filter(
        user_id=date_class['user_id'],
        sp_date=date_class['sp_date']
    )[1:].update(is_active=False)

If you want to avoid having duplicate set of multiple fields, i suggest taking a look at unique_together for model validation

Django List and Detail Views for rows with duplicate fields

I finally found a solution to my own problem :-)
So what I did is create a function in my views that takes an extra argument, then passes that argument to the URL and to the template as follows

views.py

def school_detail(request, school_name):
    filtered_records = Application.objects.filter(school_name=school_name)
    context = {
        'repeated_names' : filtered_records,
        'duplicates' : duplicates,
        'title' : 'Disbursement Details',
    }

    return render(request, 'calculations/detail.html', context)

Then in my urls.py

path('list/<str:school_name>/', default_views.school_detail, name='dup_detail')

And finally in my template:

  {% for application in duplicates %}
             <tr class="clickabe-row" data-target="{% url 'dup_detail' application.school_name %}">
                <td>{{ forloop.counter}}</td>
                <td>{{ application.school_name}}</td>
                <td>{{ application.name_count}}</td>


             </tr>
                {% endfor %}

Works like a charm.

How to delete rows with duplicate columns in django

I ended up doing this.

from django.db.models import Count
duplicate_foo = Foo.objects.values('req_group','child_program__id').annotate(id_c=Count('id')).filter(id_c__gt=1)
for dups in duplicate_foo:
    for i, val in enumerate(Foo.objects.filter(first=dups['first'],                                                      
                                               second=dups['second'])):
        if i ==0:
            continue
        val.delete()

Not the most optimzed solution. But it works.

---

Related Topics