Django: get duplicates based on annotation

Question

I want to get all duplicates based on a case insensitive field value.

Basically to rewrite this SQL query

SELECT count(*), lower(name)
FROM manufacturer
GROUP BY lower(name)
HAVING count(*) > 1;

with Django ORM. I was hoping something like this would do the trick

from django.db.models import Count
from django.db.models.functions import Lower

from myapp.models import Manufacturer


qs = Manufacturer.objects.annotate(
    name_lower=Lower('name'),
    cnt=Count('name_lower')
).filter('cnt__gt'=1)

but of course it didn't work.

Any idea how to do this?

Manufacturer.objects.values(name).annotate( cnt=Count('name_lower') ).filter('cnt__gt'=1) — Serg Anuke, Oct 25 '17 at 13:42
@SergAnuke that will not work as `name_lower` isn't an actual column but comes from annotation — Dušan Maďar, Oct 25 '17 at 13:44

Brown Bear · Accepted Answer · 2017-10-25T14:22:16.470

9

you can try it:

qs = Manufacturer.objects.annotate(lname=Lower('name')
     ).values('lname').annotate(cnt=Count(Lower('name'))
     ).values('lname', 'cnt').filter(cnt__gt=1).order_by('lname', 'cnt')

why should add the order_by ordering-or-order-by:

the sql query looks like:

SELECT 
    LOWER("products_manufacturer"."name") AS "lname",
    COUNT(LOWER("products_manufacturer"."name")) AS "cnt"
FROM "products_manufacturer"
GROUP BY LOWER("products_manufacturer"."name")
HAVING COUNT(LOWER("products_manufacturer"."name")) > 1
ORDER BY "lname" ASC, "cnt" ASC

edited Oct 25 '17 at 14:22

answered Oct 25 '17 at 14:01

Brown Bear

19,655
10
58
76

This works, thank you! Though, it doesn't work if `order_by('lname', 'cnt')` isn't part of the query. Do you know why? Also, `annotate(cnt=Count(Lower('name'))` can be simplified to `annotate(cnt=Count('lname')`. – Dušan Maďar Oct 25 '17 at 14:13
Order in which `annotate()` and `values()` clauses are applied to a query is significant: https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#order-of-annotate-and-values-clauses – Dušan Maďar Oct 25 '17 at 14:24

Django: get duplicates based on annotation

1 Answers1