2

I am working on counting rows with duplicate values in a django Model and running into a little hitch. I have looked at this question Django Select Rows with duplicate Values

and the solution is perfect. But my question is, What if I want to Count the rows and group them according to: Name of the duplicated row, and how many times it has been duplicated, instead of just displaying the name of the repeated rows.

my code so far:

views.py

dupes = Application.objects.values('school_name').annotate(Count('id')).filter(id__count__gt=1)
query = Application.objects.filter(school_name__in=[item['school_name'] for item in dupes])

The query variable returns a queryset with the Applications that have duplicate values for the field school_name while this line:

repeated_names = Application.objects.values('school_name', 'category', ).annotate(Count('id')).order_by().filter(id__count__gt=1)

returns a list of the duplicated school names.

What I expect is something like:

(school_name=school_name_1, count=2), (school_name=school_name_2, count=3).. etc.

Abedy
  • 361
  • 5
  • 17

1 Answers1

3

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 %}
ans2human
  • 2,300
  • 1
  • 14
  • 29
  • This worked with the `client_` ommitted. But is there no way I way I can do this in the views? – Abedy Feb 19 '19 at 08:08
  • And also, how can I get access to the values of other fields on the same row? – Abedy Feb 19 '19 at 08:27
  • @Abedy Yes Updated the answer, You're already doing it in the views. To get rest of the fields data i have updated the query. Please check – ans2human Feb 19 '19 at 08:49
  • The updated query seems to have a bug. It only displays one item. – Abedy Feb 19 '19 at 09:04
  • @Abedy update the question with query you are using now and also the error you're getting. I'll check. – ans2human Feb 19 '19 at 09:05
  • @Abedy What does the bug say? – ans2human Feb 19 '19 at 09:09
  • No message, just that when I run my server, only one item, (the first item with duplicates) is displayed. The rest of the items are not. – Abedy Feb 19 '19 at 09:13
  • Okay, so do you have only `ONE` object(`school_name`) in db with duplicates? – ans2human Feb 19 '19 at 09:15
  • I have my model `Application` with the fields, `school_name` , `category` , among other fields. The `school_name` field can have duplicates. Currently I have 3 sets of duplicates, i.e object 1 and 2 have the same `school_name` , 3 and 4 another similar `school_name`. The Updated Query only gives me the school name for the first duplicate and the count. Doesn't account for the others – Abedy Feb 19 '19 at 09:24
  • Now that works as I'd like it to, only that it displays a single entry at a time. Like, it doesn't add the number of duplicates to come up with a count for each duplicate. But I can work my way around that. Thanks big time @ans2human – Abedy Feb 19 '19 at 09:37
  • @Abedy Welcome. – ans2human Feb 19 '19 at 09:39