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”.

postgres mysql sqllite oracle
like 0:00:50.727005 0:00:14.025656 0:01:36.089407 ?:??:??.??????
regex 0:01:38.410019 0:02:57.255685 0:09:39.527765 ?:??:??.??????
 improvement  254%  600%  503%  ???%

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:

Tagged with:          
 

  1. P. van Kampen says:

    Would this not work?

    User.objects.extra(where=["username like='10%%8%%0%%'"])

    (You have to escape the % but otherwise it is pretty much the SQL you want executed. )

  2. Pablo Martín says:

    This would be without the “=” symbol. But this was only a example. Imagine something like this:

    User.objects.filter(Q(groups__name__ilike='john %') |
                        Q(username__ilike='john%'))
    

    Maybe it is possible to do with extra/where. Even you can to do it without ORM. But I don’t think that was more readable

    Thanks

  3. Web Center Design.com - Campinas | Criacao de Sites | WebDesign | Publicidade | Propaganda | Marketing | Otimizacao de Sites | Logica Digital | Cartao de visita | Panfletos | Folders | Impressos | Grafica says:

    I’ll immediately take hold of your rss as I can not find your e-mail subscription hyperlink or newsletter service. Do you have any? Kindly allow me recognise in order that I may just subscribe. Thanks.

  4. publicidade says:

    Wow, marvelous weblog format! How lengthy have you ever been running a blog for? you made blogging glance easy. The full look of your site is great, let alone the content material!

  5. design says:

    Great beat ! I wish to apprentice whilst you amend your web site, how can i subscribe for a blog website? The account aided me a acceptable deal. I were a little bit acquainted of this your broadcast offered brilliant clear concept

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>