Django aggregation and query set API

This article summarizes the main usage of aggregation and the common methods of query sets from the Django official documentation.

Polymerization

1. Aggregation is generated from the Django database query, usually we use Django query to complete additions and deletions, but sometimes more complex methods to complete the data extraction, filtering, change, so need a set of object aggregation to complete this operation. Examples of models are:

 fromDjango.dbImportModelsclassAuthor (models. Model): Name= Models. Charfield (max_length=100) Age=models. Integerfield ()classPublisher (models. Model): Name= Models. Charfield (max_length=300) Num_awards=models. Integerfield ()classBook (models. Model): Name= Models. Charfield (max_length=300) pages=models. Integerfield () Price= Models. Decimalfield (max_digits=10, decimal_places=2) Rating=models. Floatfield () Authors=models. Manytomanyfield (Author) Publisher=models. ForeignKey (Publisher) pubdate=models. Datefield ()classStore (models. Model): Name= Models. Charfield (max_length=300) Books=models. Manytomanyfield (book) Registered_users= Models. Positiveintegerfield ()

View Code

Based on the given model, three examples are introduced first:

 #   total Number of books.  >>> Book.objects.count ()  2452#  Span style= "COLOR: #008000" > Total number of books with publisher=baloneypress  >>> Book.objects.filter ( Publisher__name= " baloneypress   " ). Count ()  ( 

 >>> from  Django.db.models import avg>>> book< Span class= "O". objects. All () . Aggregate (avg ( ' price ' { ' price__avg ' : 34.35}   

Note several points:. All () and. Filter () and. Count () and. Aggregate () are methods, the first of which is the method that returns a new query set (the parentheses have parameters), and the latter is a method that does not return a query set. Publisher is obviously the field name, name is the field lookup, and the two are concatenated with double underlines: __ Price is also the field name, and AVG is the aggregate function, which is used to average. The methods , field lookups , and aggregation functions mentioned above are described in the query set API . So, let's introduce aggregations.

The 2.django provides two ways to generate aggregations

1) Generate statistical values from the entire query set , main usage: aggregate (*args, **kwargs)

aggregate () is a terminating clause in QuerySet , which means that aggregate returns a dictionary containing aggregated values (averages, and so on) calculated from QuerySet . each parameter of aggregate () specifies the value that will be included in the returned dictionary. Eg:

Book.objects.all () # Returns a collection of all books
>>> fromDjango.db.modelsImportAVG # introduces an aggregate function for averaging AVG>>> Book.objects.all (). Aggregate (AVG (' Price') # To calculate the average price for all books, implement {by appending the aggregate () clause after the query set.'Price__avg': 34.35} # Returns a dictionary>>> Book.objects.aggregate (AVG (' Price') # All () is superfluous here and can be omitted {'Price__avg': 34.35}                     # returns the dictionary, the key is the aggregate value identifier, is automatically generated by the field and the name of the aggregate function, the value is the calculated aggregate value

>>> Book.objects.aggregate (average_price=avg ('price'))
{'average_price': 34.35} # Replace name for aggregated value, provide parameter Average_price

>>> fromDjango.db.modelsImportAVG, Max, Min # generates more than one aggregation
>>> Book.objects.aggregate (AVG (' Price'), Max (' Price'), Min (' Price'))
{'Price__avg': 34.35,'Price__max': Decimal ('81.20'),'Price__min': Decimal ('12.99')}

2) For each of the query set aggregation, main usage: Annotate (*args, **kwargs)

This method generates a separate summary value for each object, for example, if you are retrieving a list of books, you may want to know how many authors are involved in each book. each book and author is a many-to-many relationship. We want to summarize this relationship in every book in QuerySet. The result of a summary of objects individually can be generated by the annotate () clause. when the annotate () clause is specified, each object in theQuerySet is injected with a specific value. the syntax for these annotations is the same as that used for the aggregate () clause. each parameter of annotate () describes the aggregation that will be evaluated.

#Build an annotated queryset>>> fromDjango.db.modelsImportCount>>> q = Book.objects.annotate (Count ('authors') # and the aggregate syntax is the same, the difference is that the annotate return q is the individual objects, can be q[0],q[1] and so on to remove the object#interrogate the first object in the Queryset>>>Q[0] # Returns the first book object<book:the Definitive Guide to Django>>>>Q[0].authors__count # The number of authors who wrote the first book is 2, authors is the field name in the books model, and count is the field query, so use double underlines to connect2#interrogate the second object in the Queryset>>> q[1] # Returns the second book object<book:practical Django projects>>>> q[1].authors__count1>>> q = Book.objects.annotate (Num_authors=count ('authors') # provides a custom num_authors alias instead of the Authors__count>>>q[0].num_authors2>>> q[1].num_authors1

Unlike aggregate () , annotate () is not a terminating clause. the return result of the annotate () clause is a query set (QuerySet); this QuerySet can be used with any QuerySet method to modify, including filter (), order_by (). Find out the difference between aggregate and annotate usage, again, as follows (when you specify an aggregate field in an aggregate function, Django allows you to use the same double underline indicates an association relationship,):

>>> fromDjango.db.modelsImportMax, Min>>> Store.objects.annotate (Min_price=min ('Books__price'), Max_price=max ('Books__price') # Find the price range for books offered by each store
>>> Store.objects.aggregate (Min_price=min ('Books__price'), Max_price=max ('Books__price') # Find the cheapest book in all bookstores and the price of the most expensive book
>>> Store.objects.aggregate (Youngest_age=min ('Books__authors__age'))
# to find the minimum age for all authors in all bookstores by using a double underline to extend the relationship chain

3. Aggregations and other query clauses

Filter () and exclude ()

>>> fromDjango.db.modelsImportCount, Avg>>> Book.objects.filter (name__startswith="Django"). Annotate (Num_authors=count ('authors') # When using the annotate () clause, the filter has the effect of restricting the annotation object.  For example, get the total number of book authors that start with "Django" for the title
>>> Book.objects.filter (name__startswith="Django"). Aggregate (AVG (' Price') # When using the aggregate () clause, the filter has the effect of limiting the aggregation object.  For example, calculate the average price for all books that start with "Django" as the title

Book.  Objects.  Annotate(num_authors=Count(' authors ')).  Filter(num_authors__gt=1)          

# get more than one author's book

Note the order of the annotate () and filter () clauses above, and the order of the different query results will be different (the latter filters the publisher to a subset of the former.) ）：

>>> Publisher.objects.annotate (Num_books=count ('book')). Filter (book__rating __gt=3.0)
# The publishers who have at least published a good book (with a score of more than 3 points) include all the books that publishers publish! (Every publisher in these publishers just publishes a >3 book, even if)
>>> Publisher.objects.filter (book__rating__gt=3.0) Annotate (Num_books=count (' Book '))

# The publishers who have at least published a good book (with a score of more than 3 points) only contain publishers who have issued good books! (All book ratings issued by each publisher in these publishers must be >3)

Order_by ()

>>> Book.objects.annotate (Num_authors=count ('authors')). Order_by (' num_authors ') # Sort by the number of authors per book

VALUES ()

 >>> Author.objects.annotate (Average_rating=avg (  " book__rating   "   >>> Author.objects.values ("  name   "). Annotate (Average_rating=avg ( '  book__rating   ")" 
 # The author first groups by name, meaning that if two authors have the same name, the query results are merged! , the two are counted as a 
 
  

Author.  Objects.  Annotate(average_rating=Avg(' book__rating ')).  Values(' name ' 'average_rating ')

This code swaps the value and average order, and adds a unique field to each author . but only the author name and average_rating annotations are returned in the output results

4. Query set (QuerySet) API query

Essentially, you can create, filter, slice, and pass query sets without actually manipulating the database. No actual database operations will occur until you evaluate the query set. Can be iterated, sliced, serialized/Cached, repr (), Len (), list (), BOOL ()

1) return a new query set method

Filter (): Returns a new QuerySetcontaining the object that matches the given query parameter.

Exclude (): Returns a new QuerySetthat contains objects that do not satisfy the given lookup parameters.

Annotate (*args, **kwargs): uses the provided query expression to annotate each object in the query set.

Order_by (*fields): By default,QuerySet is sorted according to the ordering option of the model Meta class. You can use the order_by method to specify a specific sort for each QuerySet .

...

QuerySet API Reference

Django aggregation and query set API