Нескучные запросы с Django ORM Annotate и Query Expressions
Было когда-то время, когда 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+$')Получим вот такой список:
[
Дальше интереснее. 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 принимает три аргумента:
- Обёрнутое в F () имя поля, которое мы модифицируем (на самом-деле, значение этого поля будет передано в substring)
- Шаблон, по которому происходит поиск подстроки
- Имя функции 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 (). В результате, получили список целых чисел, а не строк.
