How to make a "LIKE" in a DJANGO ORM query?

Asked

Viewed 1,159 times

4

To make a query to get the data accurately in Django, I use the filter, thus:

usuarios = Usuarios.objects.filter(nome='Jonh')

This will generate an SQL similar to:

SELECT * FROM usuarios wHERE nome = 'Jonh'

As I’ve been using Django for a little while, I’d like to know in case how I would do to make a LIKE.

The database I am using is Mysql.

mysql python django orm

2 answers

6


There are some ways to execute this query:


Case sensitive

  1. Using the contains: usuarios = Usuarios.objects.filter(nome__contains='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome LIKE '%Jonh%';

  2. Using the startswith: usuarios = Usuarios.objects.filter(nome__startswith='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome LIKE 'Jonh%';

  3. Using the endswith: usuarios = Usuarios.objects.filter(nome__endswith='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome LIKE '%Jonh';


Case insensitive

  1. Using the icontains: usuarios = Usuarios.objects.filter(nome__icontains='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome ILIKE '%Jonh%';

  2. Using the istartswith: usuarios = Usuarios.objects.filter(nome__istartswith='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome ILIKE 'Jonh%';

  3. Using the iendswith: usuarios = Usuarios.objects.filter(nome__iendswith='Jonh')

    • SQL: SELECT * FROM usuarios WHERE nome ILIKE '%Jonh';


The use of double underscore (__), defined by the constant LOOKUP_SEP is used by the ORM - Object-Relational Mapping - for the generation of SQL queries, separating the variable from the query command, as in the LIKE example: nome__iendswith='Jonh' interpreted to nome ILIKE 'Jonh%'.

  • 2

    I believe the two situations you indicated the endswith are having an error in the positioning of the %. Since the % indicates anything to that side, so in those parts, he should be on the left of the term sought, not on the right. If I’m wrong, disregard.

  • 1

    copy paste, adjust, thank you.

3

I did some research and found this answer here.

You can do it this way:

Usuarios.objects.filter(nome__contains = "Jonh")

And according to his own answer, __contains is case sensitive and can be used __icontains to ignore the case (uppercase/minuscule)

  • Interesting. Whenever I want to use a "feature" I need to put this double undescore?

  • I have no idea, I just researched and found this answer.

  • 2

    The double underscore is the way Django’s ORM found to allow you to put the name of the direct operator as a parameter for a call - on the other side (within Django’s code), it makes a "split("__"), qyue breaks the parameter name into two - the first part has the field name, and the second the operator name)

  • 1

    Sqlalchemy, the most popular ORM for Python, does it differently: it overloads operators (==, >=, in, etc)... of the objects that are the fields in the tables - so instead of simply doing the operation, Python generates an object that is used to mount the SQL query. But in that case, it would look something like this ...query().filter("John" in Usuarios.nome).all

  • @jsbueno sensational, dude. Thank you so much

Browser other questions tagged mysql python django orm

You are not signed in. Login or sign up in order to post.