Было когда-то время, когда ORM Django считалась очень милой, но абсолютно глупой. Хотя, возможность производить Annotate и Aggregate были в ней с незапамятных времён. А в версии 1.8 добавилась возможность применять функции базы данных внутри Query Expressions. И, разумеется, если начинающий джангист не испугался и дочитал вступление до этих строк, он может смело читать дальше: статья ориентирована именно на начинающих.

Некоторое время назад передо мной встала задача: выбрать из таблицы значения по пользователям. Причём, эти значения должны соответствовать определённому регулярному выражению. Но и это не конец условия: из выбранных выражений нужно вытащить substring. Опять же, по регулярке. Сделал я это довольно быстро, и захотелось поделиться опытом с тем, кто ещё не может применять Annotate и Query Expressions на практике

Попробую описать ситуацию точнее:

У нас есть почти стандартная модель Users. Часть пользователей имеет различные usernames. Например, manager, vasyaTheDirector, vovaProg и т.д. А вот коммерческие пользователи имеют имена в формате {CountryCode}{RandomUniqueNumber}. Например, RU2525 или ES1672. Вот нам надо вытащить из базы всех коммерческих пользователей, но вытащить не всю информацию, а только уникальные номера без кодов стран.

Задача, безусловно, интересная для начинающих джангистов. Хотя, и для разработчиков среднего звена она может быть не совсем типичной.

Начнём мы с простого: для получения всех пользователей, имена которых начинаются с двухбуквенного кода страны, можно использовать простую операцию filter с ключом __iregex на имени поля.

from django.contrib.auth import get_user_model

User = get_user_model()

queryset = User.objects.filter(username__iregex=r'^[A-Z]{2}\d+$')

Получим вот такой список:

[<User: RU123>, <User: RU124>, <User: RU125>, <User: EN123>, <User: EN124>, <User: EN125>, <User: EN126>, <User: UK123>, <User: UK124>, <User: UK1234>, <User: UK12345>]

Дальше интереснее. Django позволяет создавать аннотации для получаемых значений. Например, нам нужно посчитать число Books, которые связаны с User посредством ForeignKey. Мы можем выполнить User.books.all()count(), либо получить значение сразу в Queryset, использовав Annotate. Мы объявим поле books_count, которое будет нам доступно, как свойство полученного инстанса User, либо как ключ словаря. Давайте, посмотрим как это будет выглядеть не на абстрактном примере с книгами, а в разрезе нашей задачи.

from django.db.models import Func

queryset = User.objects.annotate(username_index=Func()).filter(username__iregex=r'^[A-Z]{2}\d+$')

В Django имеются различные функции для аннотации значений. Например, Max, Min, Avg, Count. Они составляют часть механизма Query Expressions. Эти особые выражения могут использоваться как для описания запроса, так и для изменения values при их получении. С версии 1.8 у нас появляется возможность использовать встроенные функции базы данных. К примеру, нам нужно произвести модификацию полученных строк. Значит, мы будем применять функции, связанные с регулярными выражениями.

Я использую PostgreSQL версии 9.5, следовательно, мне нужно найти функцию, которая вытащит мне подстроку из строки. Находим эту функцию в официальной документации. Функция так и называется: substring.

from django.db.models import Func, F, Value

queryset = User.objects.annotate(username_index=Func(F('username'), Value('(\d+)'), function='substring'))).filter(username__iregex=r'^[A-Z]{2}\d+$')

Как видите, Func принимает три аргумента:

1. Обёрнутое в F() имя поля, которое мы модифицируем (на самом-деле, значение этого поля будет передано в substring)
2. Шаблон, по которому происходит поиск подстроки
3. Имя функции PostgreSQL, которой будут переданы предыдущие аргументы

Ну и нам осталось получить значения в виде списка:

from django.db.models import Func, F, Value

queryset = User.objects.annotate(username_index=Func(F('username'), Value('(\d+)'), function='substring'))).filter(username__iregex=r'^[A-Z]{2}\d+$').values_list('username_index', flat=True)

Получаем такой вывод:

['123', '124', '125', '123', '124', '125', '126', '123', '124', '1234', '12345']

Соответственно, если нам нужно будет получить уникальные номера пользователей для конкретной страны, меняем

username__iregex=r'^[A-Z]{2}\d+$'

на

username__iregex=r'^RU\d+$'.

Ну а теперь самое интересное. Как вы думаете, какой SQL запрос выполняет наш код?

SELECT substring("my_users_user"."username", (\d+)) AS "username_index" FROM "my_users_user" WHERE "my_users_user"."username"::text ~* ^[A-Z]{2}\d+$

Как видите, запрос красивый и не нуждается в срочной реанимации оптимизации.

Возвращаясь к теме проблем DJango ORM, обозначенной в начале статьи, хочется подчеркнуть, что Annotate и Aggregate существуют в Django очень давно. И, получается, просто не все умели их готовить. Хотя, возможность исполнять функции Database без написания SQL запросов, появилась сравнительно недавно. И мы можем делать ещё более красивые вещи.

P.S.
Если вам захочется получить данные в определённом формате, вы можете модифицировать код следующим образом:

from django.db.models import IntegerField, ExpressionWrapper
from django.db.models import Func, F, Value

queryset = User.objects.annotate(username_index=ExpressionWrapper(Func(F('username'), Value('(\d+)'), function='substring'), output_field=IntegerField()))).filter(username__iregex=r'^[A-Z]{2}\d+$').values_list('username_index', flat=True)

Вывод будет таким:

[123, 124, 125, 123, 124, 125, 126, 123, 124, 1234, 12345]

Мы обернули Func() в ExpressionWrapper и указали ожидаемый тип данных в output_field=IntegerField(). В результате, получили список целых чисел, а не строк.