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 listUse 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 RegExpUse 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