I want to query the database using a WHERE clause like this in Django ORM:
WHERE LOWER(col_name) = %s
or
WHERE LOWER(col_name) = LOWER(%s)
How can I do this using QuerySet API?
1 Answer
You can use extra to add a custom where condition using sql functions:
Author.objects.extra(where=["lower(name) = lower(%s)"], params=['Fabio'])
You want to use params instead of embedding the value directly in the query to avoid SQL injection by letting Django escape the params for you.
If you can avoid using the sql LOWER function on the param you pass (in my example 'Fabio'), then you can use annotate instead:
Author.objects.annotate(name_lower=Lower('name')).filter(name_lower=your_name.lower())
Note that your_name.lower() is using the python lower function, not the sql one.
I couldn't find a way to use annotate together with F() or Lower() (from django.db.models.functions import Lower) to apply Lower to your custom input, as opposed to another field.
3 Comments
planetp
Is it possible to exclude
name_lower from the fields in SELECT ?
fabio.sussetto
@planetp what do you mean? If you use the
extra example, there shouldn't be any additional fields added to the SELECT <fields> part of the query. Annotate would select the field thoughplanetp
Yep,
extra() works for me. annotate() adds the fields to the SELECT list which is undesired (I only want to filter on the expression, not to select it).
__iexactfor case-insensitive comparison instead?LOWER(col_name).