annotate django что это
Нескучные запросы с Django ORM Annotate и Query Expressions
Было когда-то время, когда ORM Django считалась очень милой, но абсолютно глупой. Хотя, возможность производить Annotate и Aggregate были в ней с незапамятных времён. А в версии 1.8 добавилась возможность применять функции базы данных внутри Query Expressions. И, разумеется, если начинающий джангист не испугался и дочитал вступление до этих строк, он может смело читать дальше: статья ориентирована именно на начинающих.
Некоторое время назад передо мной встала задача: выбрать из таблицы значения по пользователям. Причём, эти значения должны соответствовать определённому регулярному выражению. Но и это не конец условия: из выбранных выражений нужно вытащить substring. Опять же, по регулярке. Сделал я это довольно быстро, и захотелось поделиться опытом с тем, кто ещё не может применять Annotate и Query Expressions на практике
Попробую описать ситуацию точнее:
У нас есть почти стандартная модель Users. Часть пользователей имеет различные usernames. Например, manager, vasyaTheDirector, vovaProg и т.д. А вот коммерческие пользователи имеют имена в формате
Задача, безусловно, интересная для начинающих джангистов. Хотя, и для разработчиков среднего звена она может быть не совсем типичной.
Начнём мы с простого: для получения всех пользователей, имена которых начинаются с двухбуквенного кода страны, можно использовать простую операцию filter с ключом __iregex на имени поля.
Получим вот такой список:
Дальше интереснее. Django позволяет создавать аннотации для получаемых значений. Например, нам нужно посчитать число Books, которые связаны с User посредством ForeignKey. Мы можем выполнить User.books.all()count(), либо получить значение сразу в Queryset, использовав Annotate. Мы объявим поле books_count, которое будет нам доступно, как свойство полученного инстанса User, либо как ключ словаря. Давайте, посмотрим как это будет выглядеть не на абстрактном примере с книгами, а в разрезе нашей задачи.
В Django имеются различные функции для аннотации значений. Например, Max, Min, Avg, Count. Они составляют часть механизма Query Expressions. Эти особые выражения могут использоваться как для описания запроса, так и для изменения values при их получении. С версии 1.8 у нас появляется возможность использовать встроенные функции базы данных. К примеру, нам нужно произвести модификацию полученных строк. Значит, мы будем применять функции, связанные с регулярными выражениями.
Я использую PostgreSQL версии 9.5, следовательно, мне нужно найти функцию, которая вытащит мне подстроку из строки. Находим эту функцию в официальной документации. Функция так и называется: substring.
Как видите, Func принимает три аргумента:
Ну и нам осталось получить значения в виде списка:
Получаем такой вывод:
[‘123’, ‘124’, ‘125’, ‘123’, ‘124’, ‘125’, ‘126’, ‘123’, ‘124’, ‘1234’, ‘12345’]
Соответственно, если нам нужно будет получить уникальные номера пользователей для конкретной страны, меняем
Ну а теперь самое интересное. Как вы думаете, какой SQL запрос выполняет наш код?
Как видите, запрос красивый и не нуждается в срочной реанимации оптимизации.
Возвращаясь к теме проблем DJango ORM, обозначенной в начале статьи, хочется подчеркнуть, что Annotate и Aggregate существуют в Django очень давно. И, получается, просто не все умели их готовить. Хотя, возможность исполнять функции Database без написания SQL запросов, появилась сравнительно недавно. И мы можем делать ещё более красивые вещи.
P.S.
Если вам захочется получить данные в определённом формате, вы можете модифицировать код следующим образом:
[123, 124, 125, 123, 124, 125, 126, 123, 124, 1234, 12345]
Мы обернули Func() в ExpressionWrapper и указали ожидаемый тип данных в output_field=IntegerField(). В результате, получили список целых чисел, а не строк.
Prefetch
Метод prefetch_related позволяет нам уменьшить количество запросов к БД, если вы хотите выгрузить связанные с моделью данные в отношении один ко многим или многие ко многим. Допустим, есть у нас две модели:
Если мы хотим выгрузить всех авторов вместе со всеми книгами и сократить количество запросов до минимума, мы сделаем так:
При такой конструкции джанго будет использовать два запроса к БД:
чтобы стянуть всех авторов;
чтобы стянуть все книги, привязанные к этим авторам (а также это привяжет книги к авторам).
И тогда мы сможем обращаться к книгам автора:
С точки зрения SQL это будет выглядеть так: первый запрос:
И тут мы встретимся с первой неожиданностью. Такая конструкция на каждого автора будет делать дополнительный запрос, чтобы стянуть все привязанные к нему книги начинающиеся на “А”. А то, что вы “спрефетчили” в первой строке вообще будет проигнорировано. Это происходит потому, что данные загруженные через prefetch_related кешируются только для запросов типа all (смотри первый пример). Т.е. никаких фильтров, аннотаций и прочих прелестей использовать нельзя. Вторая идея, которая может возникнуть, выглядит так:
И снова будем неправы, ибо такой запрос не просто отфильтрует нам книги, но также уберет из ответа всех авторов, у которых нет книг начинающихся на “А”. В общем, чтобы работать с данными, загружаемыми через prefetch_related, предусмотрен специальный класс Prefetch, который позволяет нам превратить поведение prefetch_related в некое подобие annotate, но для связных данных.
Класс Prefetch
Чтобы наверняка решить проблему, возникшую выше, необходимо воспользоваться классом Prefetch, который позволяет фильтровать, добавлять аннотации и т. д. к связным наборам. Делается это так:
У конструктора Prefetch три параметра:
Стоит уточнить, что по дизайну в аргументе queryset полагается использовать ORM запрос с той же моделью что и у связанного набора, но если вам вдруг захочется подставить что-то другое, то возможно оно и сработает, но не факт, что так как ожидаемо, или вообще с ошибкой:
В общем, не надо так.
Также есть ограничение на использование метода values в ORM запросе. Так, попробовав вызвать:
вы получите ошибку:
Зато only можно использовать во всей красе. Ну и самый замечательный параметр, так это to_attr. Используя его, вы можете добавлять сколько угодно атрибутов, каждый из которых будет содержать независимый набор данных, например:
Еще раз прошу обратить внимание, что при доступе к prefetch-наборам, мы всегда обязательно используем метод all().
Метод raw
Это штуковина остается на крайний случай когда все остальные способы уже перепробованны. Главным условием является, чтобы в запросе, в результате присутствовало поле id.
Допустим у вас хитрый join с несколькими условиями, и какое-то поле, которое формируется на основании этого джоина. Что-то в этом роде:
и после этого я могу обращатся к полю limit_real, как будето оно является частью модели Table (то есть примерно как ведет себя функция annotate):
Также не стоит забывать, что Django в этом случае не подтягивает поля не указанные в raw-запросе. И попытка обратится к полю, которое не было указано в запросе, повлечет за собой дополнительное обращение к БД.
Главным недостатком этого метода, на мой взгляд, является не возможность использовать chain-методы. То есть навесить лишний filter, annotate, order_by, не получится. Хотите добавить условную фильтрацию извольте работать со текстом запроса вручную.
Функция RawSQL
Более мягким вариантом, когда без сложного запроса не обойтись, но хочется сохранить функционал filter, annotate, order_by, является использование RawSQL. В этом случае вам необходимо сформировать запрос который возвращает только один столбец (в большинстве случаев это id), например:
А далее передать этот запрос в качестве условия фильтрации по id, но обернув его в RawSQL:
Такой подход повзоляет реализовать сложную фильтрация, и в тоже время практически полностью остаться в рамках Django ORM
Заключение
django, orm, python, sql
Агрегация¶
В руководстве по теме API базы данных Django описан способ использования запросов Django для создания, извлечения, обновления и удаления отдельных объектов. Однако иногда вам нужно будет получить значения, полученные путем суммирования или агрегирования коллекции объектов. В этом руководстве описаны способы создания и возврата агрегированных значений с помощью запросов Django.
В этом руководстве мы будем обращаться к следующим моделям. Эти модели используются для отслеживания инвентаря в ряде книжных онлайн-магазинов:
Шпаргалка¶
Спешите? Вот как выполнять общие агрегированные запросы, исходя из моделей выше:
Создание агрегаций по QuerySet ¶
all() в этом примере избыточен, поэтому его можно упростить до:
Создание агрегатов для каждого элемента в QuerySet ¶
Объединение нескольких агрегатов¶
Объединение нескольких агрегатов с помощью annotate() выдаст даёт неверные результаты, потому что соединения используются вместо подзапросов:
Если сомневаетесь, проверьте SQL-запрос!
Соединения и агрегаты¶
До сих пор мы имели дело с агрегатами по полям, принадлежащим запрашиваемой модели. Однако иногда значение, которое вы хотите агрегировать, будет принадлежать модели, связанной с моделью, которую вы запрашиваете.
Например, чтобы узнать ценовой диапазон книг, предлагаемых в каждом магазине, вы можете использовать аннотацию:
Цепочки присоединения могут быть настолько глубокими, насколько вам необходимо. Например, чтобы извлечь возраст самого молодого автора любой книги, доступной для продажи, вы можете ввести запрос:
Следить за отношениями в обратном направлении¶
Мы также можем попросить самую старую книгу из тех, что выпущены каждым издателем:
Или спросите средний рейтинг всех имеющихся у нас книг, написанных авторами:
Агрегации и другие предложения QuerySet ¶
filter() и exclude() ¶
Агрегаты также могут участвовать в фильтрах. Любой filter() (или exclude() ), применяемый к обычным полям модели, будет иметь эффект ограничения объектов, которые рассматриваются для агрегирования.
При использовании с предложением annotate() фильтр имеет эффект ограничения объектов, для которых рассчитывается аннотация. Например, вы можете сгенерировать аннотированный список всех книг, название которых начинается с «Django», используя запрос:
При использовании с предложением aggregate() фильтр имеет эффект ограничения объектов, по которым вычисляется агрегат. Например, вы можете сгенерировать среднюю цену всех книг с названием, начинающимся с «Django», с помощью запроса:
Фильтрация по аннотациям¶
Аннотированные значения также можно фильтровать. Псевдоним для аннотации можно использовать в предложениях filter() и exclude() так же, как и в любом другом поле модели.
Например, чтобы создать список книг, у которых более одного автора, вы можете выполнить запрос:
Этот запрос создает аннотированный набор результатов, а затем создает фильтр на основе этой аннотации.
Если вам нужны две аннотации с двумя отдельными фильтрами, вы можете использовать аргумент filter с любым агрегатом. Например, чтобы создать список авторов с количеством книг с высоким рейтингом:
Выбор между filter и QuerySet.filter()
Избегайте использования аргумента filter с одной аннотацией или агрегированием. Более эффективно использовать QuerySet.filter() для исключения строк. Аргумент filter агрегации полезен только при использовании двух или более агрегаций по одним и тем же отношениям с разными условными выражениями.
Порядок предложений annotate() и filter() ¶
Вот пример с агрегатом Count :
Оба запроса возвращают список издателей, у которых есть хотя бы одна книга с рейтингом выше 3,0, поэтому издатель C исключен.
Второй запрос подсчитывает количество книг с рейтингом выше 3.0 для каждого издателя. Фильтр предшествует аннотации, поэтому фильтр ограничивает объекты, учитываемые при вычислении аннотации.
Вот еще один пример с агрегатом Avg :
Первый запрос запрашивает средний рейтинг всех книг издателя для издателей, у которых есть хотя бы одна книга с рейтингом выше 3.0. Второй запрос запрашивает средний рейтинг книги издателя только для тех оценок, которые превышают 3.0.
Трудно интуитивно понять, как ORM будет переводить сложные наборы запросов в запросы SQL, поэтому в случае сомнений проверьте SQL с помощью str(queryset.query) и напишите множество тестов.
order_by() ¶
Например, чтобы отсортировать QuerySet книг по количеству авторов, которые внесли свой вклад в книгу, вы можете использовать следующий запрос:
values() ¶
Например, рассмотрим запрос автора, который пытается узнать средний рейтинг книг, написанных каждым автором:
Это вернет по одному результату для каждого автора в базе данных с аннотацией их среднего книжного рейтинга.
Однако результат будет немного другим, если вы воспользуетесь предложением values() :
В этом примере авторы будут сгруппированы по именам, поэтому вы получите аннотированный результат только для каждого уникального имени автора. Это означает, что если у вас есть два автора с одним и тем же именем, их результаты будут объединены в один результат в выводе запроса; среднее значение будет вычислено как среднее по книгам, написанным обоими авторами.
Порядок предложений annotate() и values() ¶
Например, если мы изменим порядок предложений values() и annotate() из нашего предыдущего примера:
Взаимодействие с сортировкой по умолчанию или order_by() ¶
В качестве примера предположим, что у вас есть такая модель:
…удаление любой сортировки в запросе. Вы также можете упорядочить, скажем, data без каких-либо вредных последствий, поскольку они уже играют роль в запросе.
Агрегирование аннотаций¶
Например, если вы хотите рассчитать среднее количество авторов на книгу, вы сначала аннотируете набор книг с указанием количества авторов, а затем объединяете это количество авторов, ссылаясь на поле аннотации:
Documentation
Aggregation¶
The topic guide on Django’s database-abstraction API described the way that you can use Django queries that create, retrieve, update and delete individual objects. However, sometimes you will need to retrieve values that are derived by summarizing or aggregating a collection of objects. This topic guide describes the ways that aggregate values can be generated and returned using Django queries.
Throughout this guide, we’ll refer to the following models. These models are used to track the inventory for a series of online bookstores:
Cheat sheet¶
In a hurry? Here’s how to do common aggregate queries, assuming the models above:
Generating aggregates over a QuerySet ¶
The all() is redundant in this example, so this could be simplified to:
aggregate() is a terminal clause for a QuerySet that, when invoked, returns a dictionary of name-value pairs. The name is an identifier for the aggregate value; the value is the computed aggregate. The name is automatically generated from the name of the field and the aggregate function. If you want to manually specify a name for the aggregate value, you can do so by providing that name when you specify the aggregate clause:
If you want to generate more than one aggregate, you add another argument to the aggregate() clause. So, if we also wanted to know the maximum and minimum price of all books, we would issue the query:
Generating aggregates for each item in a QuerySet ¶
Per-object summaries can be generated using the annotate() clause. When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.
The syntax for these annotations is identical to that used for the aggregate() clause. Each argument to annotate() describes an aggregate that is to be calculated. For example, to annotate books with the number of authors:
Combining multiple aggregations¶
Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries:
For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help:
If in doubt, inspect the SQL query!
Joins and aggregates¶
So far, we have dealt with aggregates over fields that belong to the model being queried. However, sometimes the value you want to aggregate will belong to a model that is related to the model you are querying.
When specifying the field to be aggregated in an aggregate function, Django will allow you to use the same double underscore notation that is used when referring to related fields in filters. Django will then handle any table joins that are required to retrieve and aggregate the related value.
For example, to find the price range of books offered in each store, you could use the annotation:
This tells Django to retrieve the Store model, join (through the many-to-many relationship) with the Book model, and aggregate on the price field of the book model to produce a minimum and maximum value.
The same rules apply to the aggregate() clause. If you wanted to know the lowest and highest price of any book that is available for sale in any of the stores, you could use the aggregate:
Join chains can be as deep as you require. For example, to extract the age of the youngest author of any book available for sale, you could issue the query:
Following relationships backwards¶
We can also ask for the oldest book of any of those managed by every publisher:
Or ask for the average rating of all the books written by author(s) we have on file:
Aggregations and other QuerySet clauses¶
filter() and exclude() ¶
Aggregates can also participate in filters. Any filter() (or exclude() ) applied to normal model fields will have the effect of constraining the objects that are considered for aggregation.
When used with an annotate() clause, a filter has the effect of constraining the objects for which an annotation is calculated. For example, you can generate an annotated list of all books that have a title starting with “Django” using the query:
When used with an aggregate() clause, a filter has the effect of constraining the objects over which the aggregate is calculated. For example, you can generate the average price of all books with a title that starts with “Django” using the query:
Filtering on annotations¶
Annotated values can also be filtered. The alias for the annotation can be used in filter() and exclude() clauses in the same way as any other model field.
For example, to generate a list of books that have more than one author, you can issue the query:
This query generates an annotated result set, and then generates a filter based upon that annotation.
If you need two annotations with two separate filters you can use the filter argument with any aggregate. For example, to generate a list of authors with a count of highly rated books:
Choosing between filter and QuerySet.filter()
Avoid using the filter argument with a single annotation or aggregation. It’s more efficient to use QuerySet.filter() to exclude rows. The aggregation filter argument is only useful when using two or more aggregations over the same relations with different conditionals.
Order of annotate() and filter() clauses¶
When an annotate() clause is applied to a query, the annotation is computed over the state of the query up to the point where the annotation is requested. The practical implication of this is that filter() and annotate() are not commutative operations.
Here’s an example with the Count aggregate:
Both queries return a list of publishers that have at least one book with a rating exceeding 3.0, hence publisher C is excluded.
The second query counts the number of books that have a rating exceeding 3.0 for each publisher. The filter precedes the annotation, so the filter constrains the objects considered when calculating the annotation.
Here’s another example with the Avg aggregate:
The first query asks for the average rating of all a publisher’s books for publisher’s that have at least one book with a rating exceeding 3.0. The second query asks for the average of a publisher’s book’s ratings for only those ratings exceeding 3.0.
It’s difficult to intuit how the ORM will translate complex querysets into SQL queries so when in doubt, inspect the SQL with str(queryset.query) and write plenty of tests.
order_by() ¶
Annotations can be used as a basis for ordering. When you define an order_by() clause, the aggregates you provide can reference any alias defined as part of an annotate() clause in the query.
For example, to order a QuerySet of books by the number of authors that have contributed to the book, you could use the following query:
values() ¶
For example, consider an author query that attempts to find out the average rating of books written by each author:
This will return one result for each author in the database, annotated with their average book rating.
However, the result will be slightly different if you use a values() clause:
In this example, the authors will be grouped by name, so you will only get an annotated result for each unique author name. This means if you have two authors with the same name, their results will be merged into a single result in the output of the query; the average will be computed as the average over the books written by both authors.
Order of annotate() and values() clauses¶
However, if the annotate() clause precedes the values() clause, the annotations will be generated over the entire query set. In this case, the values() clause only constrains the fields that are generated on output.
For example, if we reverse the order of the values() and annotate() clause from our previous example:
This will now yield one unique result for each author; however, only the author’s name and the average_rating annotation will be returned in the output data.
You should also note that average_rating has been explicitly included in the list of values to be returned. This is required because of the ordering of the values() and annotate() clause.
If the values() clause precedes the annotate() clause, any annotations will be automatically added to the result set. However, if the values() clause is applied after the annotate() clause, you need to explicitly include the aggregate column.
Interaction with order_by() ¶
Fields that are mentioned in the order_by() part of a queryset are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group “like” results together and they can make otherwise identical result rows appear to be separate. This shows up, particularly, when counting things.
By way of example, suppose you have a model like this:
If you want to count how many times each distinct data value appears in an ordered queryset, you might try this:
…which will group the Item objects by their common data values and then count the number of id values in each group. Except that it won’t quite work. The ordering by name will also play a part in the grouping, so this query will group by distinct (data, name) pairs, which isn’t what you want. Instead, you should construct this queryset:
…clearing any ordering in the query. You could also order by, say, data without any harmful effects, since that is already playing a role in the query.
This behavior is the same as that noted in the queryset documentation for distinct() and the general rule is the same: normally you won’t want extra columns playing a part in the result, so clear out the ordering, or at least make sure it’s restricted only to those fields you also select in a values() call.
You might reasonably ask why Django doesn’t remove the extraneous columns for you. The main reason is consistency with distinct() and other places: Django never removes ordering constraints that you have specified (and we can’t change those other methods’ behavior, as that would violate our API stability policy).
Aggregating annotations¶
You can also generate an aggregate on the result of an annotation. When you define an aggregate() clause, the aggregates you provide can reference any alias defined as part of an annotate() clause in the query.
For example, if you wanted to calculate the average number of authors per book you first annotate the set of books with the author count, then aggregate that author count, referencing the annotation field: