Lookups, Transforms and Expressions

• Anssi Kaariainen
• django core team, ORM, lookups and transforms

Lookups

Book.objects.filter(title__glob='Django*')
Book.objects.filter(title__lower__glob='*DJanGo*')
Book.objects.annotate(
  author_str=GroupConcat('authors__name')
)

Why not extra() ?

• reuseability
• chainability
• multi-database support
• nicer looking code
• easier to support

Lookup Class

• a condition in the query’s WHERE clause
• eg gt, lt ...

Create a custom lookup

• subclass models.Lookup
• set lookup_name
• implement as_sql()
• register to a Field

Demo - Glob Lookup

@models.CharField.register_lookup
class GlobLookup(models.Lookup):
    lookup_name = 'glob'

    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = self.process_lhs(compiler, connection)
        rhs_sql, rhs_params = self.process_rhs(compiler, connection)
        return "%s glob %s" % (lhs_sql, rhs_sql), lhs_params + rhs_params

    Book.objects.filter(title__glob="*Django*")

Transform - case insensitive

Transform takes in a value, outputs a transformed value

• subclass models.Transform
• set lookup_name
• implement as_sql()
• register to a Field

# note it *is* register_lookup
@models.CharField.register_lookup
class LowerTransform(models.Transform):
    lookup_name = 'lower'

    def as_sql(self, compiler, connection):
        lhs_sql, lhs_params = compiler.compile(self.lhs)
        return 'lower(%s)' % lhs_sql, lhs_params

    Book.objects.filter(title__lower__glob="*django*")

• Can apply transform multiple times eg title__lower__lower__glob
• Can apply to both sides with bilateral = True in class

Expressions

• An element in SELECT clause
• eg Avg('authors__age')

Create expression in 1.8

• subclass models.Func or models.Aggregate
• set lookup_name

# note it *is* register_lookup
class GroupConcat(models.Aggregate):
    function = 'group_concat'

    Book.objects.all().annotate(
        authors_str=GroupConcat('author__name')
    )

    # combine various things - produces complex SQL query
    Book.objects.all().annotate(
        authors_str=GroupConcat('author__name')
    ).filter(
        authors_str__lower__glob="*a,m*"
    )

New in Django 1.8

• all expressions support arithmetic operations

• all aggregates are expressions

  annotate price_pre_page = Sum ‘books__price’ / Sum ‘books__pages’

output_field

• When the type of the value changes - eg len charfield -> integerfield
• set output_field = models.IntegerField on class

annotate(
    name_len=Length('name')
).filter(name_len__gte(10))

Multiple SQL dialects

• django looks for (eg) as_postgres() method before as_sql() method
• so can use DB functions directly

Hstore and ArrayField

• in django.contrib.postgres
• HStoreField - key values

Dog.objects.create(
    name='Ruth',
    data={'breed': 'labrador', ...}
)
Dog.objects.filter(data__breed='labrador')
Dog.objects.filter(data__breed__icontains='l')

ArrayField

Post.objects.create(
    name='first post',
    tags=['thoughts', 'django']
)
filter(tags__contains=['thoughts', 'django'])
filter(tags__overlap=['thoughts', 'django'])
filter(tags__1__lower__glob='*o*')

Next

• implement common expressions in Django core/contrib
• use in more places - eg order_by
• unify expression and transform implementation
• idea - .filter(F('name').substr(0, 25).icontains('anssi'))

Much more in Django docs

https://www.djangoproject.com/fundraising/