A simple and impossible query in django

The ORM with the forms, according to my opinion, are the best and most noteworthy parts that Django has. Thanks to both these components work with this framework is easier. But, that being said, obviously, they can have errors or are not complete. In this post, I am going to propose two lookups, to complete the ORM.

Two months ago I read in stackoverflow a question that asked: “How can you make a very simple SQL query with Django ORM?, as the following”:

SELECT * FROM auth_user WHERE username LIKE '10%8%0%'

At the beginning I thought he was asking for a simple query using the lookup contains, but it was not that way. In Django you cannot get that in any way. You can only get the same result, emulating the query with regular expressions:

User.objects.filter(username__regex=r'^10.*8.*0.*$')

I realized two new lookups were necessary: “like” and “ilke”. I did a patch, and since they accepted it, I did a django-like module, so the community could test it easily. This module, through monkey-patching, makes the same that the patch. So the above query would look like this:

User.objects.filter(username__like='10%8%0%')

Reasons for

The reasons why I think that the patch should be added to the Django core are the following:

Readability

The query with “like” is more simple and shorter that with “regex”.

Performance

I think that this point is not the most important; Because even if there were no improvement, I would add these lookups, because they increase the readibility.

I show a table with the impreovement that you get after running the benchmark_like command, this compares the time to run 10.000 equivalent queries done with “like” or with “regex”.

Incomplete funcionality

The backends that Django support, by default, are postgres, mysql, sqlite and oracle. In all of them you can do a query with LIKE, so there should exist a lookup with which you can do it too.

Reasons against

This patch has not been accepted, they have given me these reasons, with which, except the first, I don’t agree

This is not generic

It is true that the name is not generic and this has much relation with the SQL syntax. But this is not a reason to reject it, this is only a reason to look for a better name. It could be: econtains, the e meaning escape.

Performance

Before I said that with LIKE the queries are more efficient than with REGEX, but they told me that this did not occur in postgres. But as you can see in the table before, there is not as big of a difference as in mysql or sqlilite, but there is a great improvement nonetheless.

Duplication of code

Another motive that they have given me, is that if you can do it with regex, why do it by another way. I am complety against the duplicity of code, I am, sometime very strict with this, as you can see in a ticket in Django that I did. But in this case, it is not a duplication of code, or duplication of funcionality, and as I said: if you think by this way, you should not use contains, startswith, endwith, etc and do the queries with regex, which is possible, but you lose in readabity and performance

Related Posts:

• How visually detect visually cyclic import between our django applicationsComo detectar visualmente importaciones cíclicas entre nuestras aplicaciones django
• How to integrate forms in the Django admin site? django-form-admin¿Cómo integrar formularios en la administración de Django? django-form-admin
• Inline Editing and translating in DjangoEditando y traduciendo inline en Django