What’s provided?

LookUps

Django offers a series of built-in lookups that can be used in queries, but sometimes one needs additional lookups to take full advantage of an underlying database. Therefore Django allows developers to define custom lookups.

This app provides the following lookups:

class postgres_utils.lookups.INotRegex(lhs, rhs)

__inoregex matches rows that do not match a case-insensitive RegExp

as_sql(compiler, connection)

Responsible for returning a (sql, [params]) tuple to be included in the current query.

Different backends can provide their own implementation, by providing an as_{vendor} method and patching the Expression:

``` def override_as_sql(self, compiler, connection):

# custom logic return super().as_sql(compiler, connection)

setattr(Expression, ‘as_’ + connection.vendor, override_as_sql) ```

Arguments:
  • compiler: the query compiler responsible for generating the query. Must have a compile method, returning a (sql, [params]) tuple. Calling compiler(value) will return a quoted value.
  • connection: the database connection used for the current query.
Return: (sql, params)
Where sql is a string containing ordered sql parameters to be replaced with the elements of the list params.
class postgres_utils.lookups.NotIn(lhs, rhs)

__notin matches rows with values not in the list

Use as follows:

qs = Model.objects.filter(somefield__notin=[value1, value2, ...])
class postgres_utils.lookups.NotRegex(lhs, rhs)

__noregex matches rows that do not match a RegExp

Use as follows:

qs = Model.objects.filter(charfield__noregex="pattern")

See also PostgreSQL: POSIX Regular Expressions

as_sql(compiler, connection)

Responsible for returning a (sql, [params]) tuple to be included in the current query.

Different backends can provide their own implementation, by providing an as_{vendor} method and patching the Expression:

``` def override_as_sql(self, compiler, connection):

# custom logic return super().as_sql(compiler, connection)

setattr(Expression, ‘as_’ + connection.vendor, override_as_sql) ```

Arguments:
  • compiler: the query compiler responsible for generating the query. Must have a compile method, returning a (sql, [params]) tuple. Calling compiler(value) will return a quoted value.
  • connection: the database connection used for the current query.
Return: (sql, params)
Where sql is a string containing ordered sql parameters to be replaced with the elements of the list params.

Database Functions

Django provides a way for users to use functions provided by the underlying database as annotations, aggregations, or filters. Functions are also expressions, so they can be used and combined with other expressions like aggregate functions.

However the list of PostgreSQL specific functions in Django is very limited.

class postgres_utils.functions.ArraySubquery(queryset, output_field=None, **extra)

Convert sub-query results to array

While Django’s original django.db.models.Subquery is allowed to return only one match, this subquery is converted into an array and can return all matches, e.g.:

sub_q = Topping.objects.filter(pizza=OuterRef("id"), vegan=True).values("name")
qs = Pizza.objects.annotate(vegan_toppings=ArraySubquery(sub_q))
Parameters:queryset – The queryset to be executed as subquery.
class postgres_utils.functions.RegexpReplace(expression, pattern, replacement, **extra)

Use regular expression to replace value in field

Note

This might become available in Django in the future: https://code.djangoproject.com/ticket/28805

Topping.objects \
    .filter(name__contains="Onion") \
    .annotate(onion_color=RegexpReplace("name", " *Onion$", ""))
Parameters:
  • expression – The expression/field to work on
  • pattern – The regular expression pattern to match
  • replacement – The replacement string for matches
class postgres_utils.functions.Substring(expression, pattern)

Use regular expression to extract a substring from field

Topping.objects \
    .filter(name__contains="Sauce") \
    .annotate(souce_type=Substring("name", "[A-Za-z]+(?= Sauce)")) \
    .values("name", "souce_type") \
    .order_by("name")
Parameters:
  • expression – The expression/field to work on
  • pattern – The regular expression pattern to match