[Django]-Django distinct on case sensitive entries

by

7👍

✅

You can use annotate in conjunction with Lower (or Upper, etc…) to normalize your values and return truly distinct values like this…

from django.db.models.functions import Lower

z = Restaurant.objects.annotate(
   city_lower=Lower('city')).values_list(
      'city_lower',flat=True).order_by('city_lower').distinct()

Note: Make sure order_by is set to ‘city_lower’ and not ‘city’ to avoid duplicates.

👤JxAxMxIxN

1👍

I’m not sure you’re going to find a solution to this since django doesn’t offer a case-insensitive distinct method (currently). But then maybe it would be better to fix the values in your database anyway since you don’t really want your end users to see their city in capitals since it will look ugly.

I’d suggest thinking about making a simple method that you could run either once in a data migration and stopping the city field from ever getting in this state again – or just running this periodically.

something similar to 

for restaurant in Restaurant.objects.all():
    if restaurant.city != restaurant.city.title():
        restaurant.city = restaurant.city.title()
        restaurant.save()
👤Sayse

0👍

Try this;

z = Restaurant.objects.extra(select = {'tmp_city': lower('city')}).values_list('city',flat=True).order_by('city').distinct('tmp_city')
👤Geo Jacob

0👍

This works, although it is a little messy. I ended up having to use values, since distinct only works on database tables, regardless of whether or not you use annotate, extra, or rawSQL.

You end up creating an extra field with annotate, and then use that field in your list of dictionaries created by values. Once you have that list of dictionaries, you can use groupby to group dictionaries based on the Lower values key in the values list of dicts. Then, depending on how you want to select the object (in this case, just taking the first object of the group), you can select the version of the distinct that you want.

from django.db.models.functions import Lower
from itertools import groupby

restaurant = [g.next() for k, g in groupby(
        list(
            Restaurant.objects.annotate(city_lower=Lower('message_text')).values_list('city', flat=True)
        ).order_by('city').values('city_lower', 'city')
    ), lambda x: x['city_lower'])]
👤knelson

Leave a comment