Нескучные запросы с Django ORM Annotate и Query Expressions

  • Tutorial

Было когда-то время, когда 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(). В результате, получили список целых чисел, а не строк.

  • +13
  • 12.4k
  • 8
Share post

Similar posts

Comments 8

    0
    Послушайте, а в чём пуля?

    Смысл ORM, как я его понимаю, в независимости от БД. Если в ORM писать БД-специфичные вещи, то переносимость сразу пойдёт по одному адресу, и проще уж тогда писать сразу RAW SQL, не выворачивая мозги с ORM, или я не прав?
      0
      И прав, и не прав. ORM не может быть независимым от БД. Это обёртка, прослойка, которая представляет запросы к БД и их результат в форме, специфичной для языка программирования. Об этом само название говорит. Object Related Model. Т.е. представление модели в виде объектов языка.

      Кроме-того, независимость ORM от БД нужна не для того, чтобы каждый день менять этот движок. Разработчики в условиях реальной работы выбирают одну БД и под неё пишут. Запросы оптимизируются, часто пишется чистый SQL. Но вот его использовать не желательно. Поскольку, это другой язык. У тебя весь код на Python, вся архитектура представлена объектами и функциями. А тут раз, и переход на SQL. Как минимум, это не красиво. Как максимум — не безопасно. А в общих чертах: если ты возьмёшь полученный запрос и попытаешься его выполнить, ты автоматически выйдешь за рамки операций с QuerySet, Lazy Connection и т.д. ты добавляешь в свой код ещё одну сущность: raw SQL.
        +1
        вообще-то ORM — Object-Relational Mapping
        <zanuda-mode-off />
          0
          Да, верно. Спасибо, что поправил. В любом случае, концепция описывается и применяется как абстракция Базы данных в виде ООП сущностей. Отсюда и рабоче-крестьянские переиначивания. Хотя, Object-Relational Mapping — исторически верное значение.
        0
        Но это чисто логическое и стилевое ограничение. Разумеется, если тебе нужно сделать что-то особое, ты выполняешь SQL. Но не тогда, когда ORM поддерживает функционал. Иначе зачем вообще ORM? Можно было бы с тем же успехом выполнять SQL запросы вместо Model.objects.all()
          0

          Код, написанный с использованием ORM намного короче и красивее, легче читается, модифицируется и его намного проще использовать повторно.


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

          0
          Код, написанный с использованием ORM намного короче и красивее, легче читается


          Я, однозначно, соглашусь, что ORM необходим как дополнительный уровень абстракции, и от него никуда не деться.

          Но «короче, красивее, легче читается» — извините. Я хорошо знаю SQL, и необходимость работать с ORM до сих пор вызывает у меня отвращение.

          Он короче и красивее, если надо написать что-то не сложнее Users.oblects.filter(name='vasya').
          А если требуется более сложный запрос, да тот же Left Join, приходится каждый раз насиловать собственный мозг. И потом перепроверять получившийся SQL — потому что зачастую он несёт серверу ад и погибель.

          P.S. Если смотреть на DAL в маргинальном Web2py, там всё сделано гораздо более человечно.
            0
            Я тоже согласен, что код на ORM не так лаконичен. Однако, следует понять для чего вообще используется ORM. А используется он для того, чтобы вынести все SQL операции в чёрный ящик, предоставив разработчику возможность работы только с объектами. Допустим, у нас нет ORM (разработчики Django решили отказаться от него). Что мы будем делать? Наша задача — найти и прикрутить существующую ORM, либо написать свою. Причин для этого много. В частности, мы не захотим постоянно выполнять запросы к БД и производить типизацию полученных значений. Скорее-всего, мы создадим нечто подобное тому, что есть сейчас. А именно, определим абстракции для таблиц, напишем для них обёртку, которая будет получать из БД данные и превращать их в свойства объекта. Реализуем паттерн Lazy connection. Т.е., по факту, создадим заново ORM для джанги.

            Итак, в начале и прежде-всего, ORM реализует работу с объектами и классами, исключая наше вмешательство в исполнение запросов и препроцессинг/постпроцессинг данных. Однако, возникают ситуации, подобные описанной в треде. И у нас возникает дилемма: либо мы вводим в нашу объектно-ориентированную архитектуру построения данных SQL костыли, либо пытаемся реализовать запрос на уровне ORM. В любом случае, костыль на SQL актуален только тогда, когда без него система не сможет работать. Например, у нас Django < 1.8. У нас есть выбор: реализовать запрос с помощью SQL, либо:

            def extract_number(username):
                # Code here
            
            numbers = [extract_number(username) for username in User.objects.filter(username__iregex=r'^KZ\d+$').values_list('username', flat=True)]
            


            И, как ни странно, именно этот вариант будет правильным до тех пор, пока код не начнёт исполняться слишком долго. Даже такое решение позволяет избавиться от инжекта чужеродного кода в скрипт.

          Only users with full accounts can post comments. Log in, please.