3

This is my query

SELECT * FROM `music` where lower(music.name) = "hello"

How can I send this query with django

I tried this but it didn't add lower in the query

>>> Music.objects.filter(name__iexact="hello")
(0.144) SELECT `music`.`id`, `music`.`name`, `music`.`artist`, `music`.`image`, `music`.`duration`, `music`.`release_date`, `music`.`is_persian` FROM `music` WHERE `music`.`name` LIKE 'hello' LIMIT 21; args=('hello',)
<QuerySet []>
Arian
  • 55
  • 1
  • 5
  • 2
    Any reason it should? `LIKE 'hello'` is sufficient to do case insensitive matching for your DB backend. – Jon Clements Mar 18 '18 at 11:28
  • What DB backend are you using? As @JonClements mentioned, it likely matches case insensitive by default. – FlipperPA Mar 18 '18 at 11:51
  • [Case-insensitive Query](https://stackoverflow.com/questions/11743207/django-model-case-insensitive-query-filtering) – JPG Mar 18 '18 at 17:10

1 Answers1

6

You can use Lower database function as below.

>>> from django.db.models.functions import Lower
>>> lower_name_music = Music.objects.annotate(lower_name=Lower('name'))
>>> lower_name_music.filter(lower_name__iexact="hello")

  • First statement is to import the database function.

  • Second statement is to add calculated column named lower_name using Lower function on name column. At this time database is not yet been queried.

  • Third statement is to filter using the calculated column. As this statement prints out result, a query is actually executed against database.

Hai Lang
  • 570
  • 2
  • 6