CODEWITHSUNDEEP
pythondjangoorm
Daniyal Syed
Daniyal Syed

Reputation: 563

Django case insensitive "distinct" query

I am using this django query

people.exclude(twitter_handle=None).distinct('twitter_handle').values_list('twitter_handle', flat=True)

My distinct query is returning two objects For example : 

['Abc','abc']

How can i get case insensitive results ? like in this case only

['abc']

using django 1.9.6, python 2.7

Upvotes: 8

Views: 3070

Answers (2)

NS0
NS0

Reputation: 6096

One solution is to use annotate to create a new field with the lower case value, then use distinct on that.

Try something like

from django.db.models.functions import Lower

(people.exclude(twitter_handle=None)
      .annotate(handle_lower=Lower('twitter_handle'))
      .distinct('handle_lower'))

Upvotes: 0

Ozgur Vatansever
Ozgur Vatansever

Reputation: 52133

You can use .annotate() along with Func() expressions to apply .distinct() on lowercased twitter_handle value:

>>> from django.db.models.functions import Lower
>>> people.order_by().exclude(twitter_handle=None).annotate(handle_lower=Lower("twitter_handle")).distinct("handle_lower")

You can't append values_list('twitter_handle', flat=True) to the above query because you can't apply distinct on a field that doesn't exist in the values_list so you have to do it by yourself:

 >>> queryset = people.order_by().exclude(twitter_handle=None).annotate(handle_lower=Lower("twitter_handle")).distinct("handle_lower")
 >>> [p.twitter_handle for p in queryset]

or you can get lowercased twitter_handle values:

>>> people.order_by().exclude(twitter_handle=None).annotate(handle_lower=Lower("twitter_handle")).distinct("handle_lower").values_list("handle_lower", flat=True)

Upvotes: 6

Related Questions