Djangocon: lookups, transforms and expressions - Anssi Kääriäinen¶

Tags: django, djangocon

(One of the summaries of a talk at the 2015 Djangocon EU conference).

(Anssi Kääriäinen gave a great talk last year at “django under the hood” about the django ORM. Look at that one, too.)

Lookups, transforms and expressions are new features in the ORM (part in 1.7, part in 1.8). They are ways to inject custom SQL in a well-structured way.

Custom lookups are things like __glob that you can write yourself in addition to the build-in ones like __gte. Custom transforms? __lower, as an example.

How you could do this previously was by using .extra(), you could tweak the sql this way. The new approach is more reusable. It can also be chained (somefield__lower__glob)! Another drawback of .extra() is that it quite a hacky part of the django code. The new functionality

Lookups. Those are basically “a condition in the query’s WHERE clause”. Examples in django are lt, gt, gte, contains and so. To create a custom lookup, you subclass models.Lookup. You set the lookup_name and you implement the .as_sql() method. Afterwards you register it for the various Field types for which it is valid.

An example (very quickly and incompletely copied over from the presentation):

@models.CharField.register_lookup
class GlobLookup(models.Lookup):
    lookup_name = 'glob'
    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = .....
        # ^^^ lhs means "left hand side", same rhs
        rhs_sql, rhs_params = .....

        return "%s glob %s" % (lhs_sql, rhs_sql) # + plus something more

# try it out
print(Book.objects.filter(title__glob='*Django'))

Transform example, in this case for lowercasing a string:

@models.CharField.register_lookup  # this will be register_transform later
class LowerTransform(models.Transform):
     lookup_name = 'lower'
     # bilateral = True
     # ^^^ Enable this if you also want to transform the right hand side.
     def as_sql(self, compiler, connection):
         lhs_sql, lhs_params = compiler.compile(....)
         return 'lower(%s)' % ...

# try it out
print(Book.objects.filter(title__lower__glob='*django'))

Expressions are an element in the SELECT clause. Subclass models.Func (or models.Aggregate) and set a method:

class GroupConcat(models.aggregate):
      function = 'group_concat'

print(Book.objects.all().annotate(autors_string=GroupConcat('authors__name'))

You can combine it all (pardon me any typos…):

Book.objects.all().annotate(
    autors_string=GroupConcat('authors__name')).filter(
    authors_string__lower__glob='linda')

You can do all sorts of queries with this that weren’t possible in earlier versions!

Some extra notes:

• You can add output_field if the field type changes. If you have a Length expression, you probably effectively convert a list of strings into one single number.

• Multiple sql dialects are supported, too. If a certain database needs special treatment, you can add methods like .as_postgres() or .as_oracle(). If available, when you use that database, they are used in preference to the generic .as_sql().

The above functionality allowed django to support some specific postgres fields for HStore, for instance:

Dog.objects.create(name='Rufus',
                   data={'breed': 'labrador',
                         'something': 'else'})
# ^^^ Data is an hstore field: key/value store.

print(Dog.objects.filter(data__breed='labrador'))

Same with a postgres list field. .filter(tags__1__lower__glob='*django')…. You can combine anything.

What’s next?

• Implement extra common expressions in django core (or in contrib).

• Ordering by transform results.

• Unify expression and transform implementations.

If you want to know more, check the django documentation: https://docs.djangoproject.com/en/1.8/ref/models/lookups/

And… a lot of this work was possible because of https://www.djangoproject.com/fundraising/, so support django!