Django ORM查询之外键、关系的反向引用

关系本身就是相互的只用在一个表中记录而不是在有关系的两个表中都记录所以外键关系提供反向引用机制当然外键可以是多个表的外键关系也可以与多个表有关系所以反向引用必须显式指出关系对方表然后是字段)。而正向引用则不必因为正向引用在定义时就指定了关系对方表(Book::publisher = ForeignKey(‘Publisher’)、Book::authors = ManyToManyField(‘Author’) )

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.

当在一个集合函数中指定要集合的字段Django将允许你像在filters中引用关联字段时那样使用两下划线符号Django之后将处理任何请求的表的连接以便检索并集合关联的值

For example, to find the price range of books offered in each store, you could use the annotation:

例如,要找到每家书店提供的书的单价范围,你可以使用注释:

#~~~~~~~

>>> from django.db.models import Max, Min

>>> Store.objects.annotate( min_price=Min('books__price'), max_price=Max('books__price') )

#对Store中单个记录,按一对多关系连接books表,对一对多的所有books的price字段进行集集合算Min()、Max()

#~~~~~~~

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.

这告诉Django检索Store模型,连接(通过多对多关系)Book模型,集合book模型的单价字段来生成最低和最高值。

Store模型:

id

name

books,存储售卖的所有书,多对多关系

Book模型:

id

name

authors,存储所有为本书贡献了的作者,多对多关系

price

Author模型:

id

name

age

 

关系就是要来告诉数据库去连接的。

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:

同样的规则也应用于集合aggregate()子句。

#~~~~~~

>>> Store.objects.aggregate( min_price=Min('books__price'), max_price=Max('books__price') )

#Store模型的books关系字段__关系连接的Book模型的price字段

#~~~~~~

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:

连接链可以如你所需要的深度那么深。例如,提取售卖的任意书的最年轻的作者的年龄,你可以这样处理查询:

#~~~~~~~~~

Store.objects.aggregate( yongest_author_age = Min(‘books__authors__age’) )

#Store模型的books关系字段__连接的Book模型的authors关系字段__连接的Author模型的age字段

#~~~~~~~~~

 

Following relationships backwards

In a way similar to Lookups that span relationships, aggregations and annotations on fields of models or models that are related to the one you are querying can include traversing “reverse” relationships. The lowercase name of related models and double-underscores are used here too.

以类似于跨关系查找的方式在模型或关联到你正在查询的模型的模型的字段上进行集合和注释可以包括遍历反向关系这里也用到了关联模型的名字的小写与两下划线

Store模型:

id

name

books,存储售卖的所有书,多对多关系

Book模型:

id

name

authors,存储所有为本书贡献乐的作者,多对多关系,多值字段

pubdate

rating

pages

publisher,外键,数据库中存储所引用的外键的主键,可以多对一关系Publisher模型记录

price

关系本身就是相互的只用在一个表中记录而不是在有关系的两个表中都记录所以外键关系提供反向引用机制当然外键可以是多个表的外键关系也可以与多个表有关系所以反向引用必须显式指出关系对方表然后是字段)。而正向引用则不必因为正向引用在定义时就指定了关系对方表(Book::publisher = ForeignKey(‘Publisher’)、Book::authors = ManyToManyField(‘Author’) )

Author模型:

id

name

age

Publisher模型:

id,主键,作为Book模型publisher字段的外键,可以一对多关系Book模型记录

name

 

For example, we can ask for all publishers, annotated with their respective total book stock counters (note how we use 'book' to specify the Publisher -> Book reverse foreign key hop):

例如,我们可以请求所有出版商,对他们对应的总同属存货计数(注意我们如何使用’book’指定Publisher ->Book反向外键跳跃):

#~~~~~~

>>> from django.db.models import Avg, Count, Min, Sum

>>> Publisher.objects.annotate( Count('book') )

#’book’——Publisher被外键引用的Book模型,对反向外键关系的Book模型的所有记录计数反向外键引用必须显式指定反向外键引用的表book统计的话不用再加字段) )

#~~~~~~

(Every Publisher in the resulting QuerySet will have an extra attribute called book__count.)

结果查询集中的每个Publisher将具有一个特殊的属性(注释列,只存在于返回的查询集上)称作book__count。

We can also ask for the oldest book of any of those managed by every publisher:

我们也可以请求每个出版商管理的书籍中最老的书(Book模型的publisher外键引用Poblish模型,为此Poblisher模型需要反向外键引用Book模型):

#~~~~~~~~~

我想的:Publisher.objects.annotate( oldest_book = Min(‘book__pubdate’) )

#为Publisher模型的每个记录注释一个’oldest_book’,对反向外键关系Book模型的pubdate最小的那个book实例反向外键引用必须显式指定反向外键引用的表book,再加其一个字段

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

#官档为Publisher模型的每个记录返回一个包含名字’oldest_pubdate’的字典,值就是反向关系(一个Publisher对应多个Book的)

#~~~~~~~~~~

返回的结果字典将有一个称作’oldest_pubdate’的键如果不指定别名将会是很长的’book__pubdate_min’(包含引用链的字段名__集合类名(集合函数名) )

This doesn’t apply just to foreign keys. It also works with many-to-many relations. For example, we can ask for every author, annotated with the total number of pages considering all the books the author has (co-)authored (note how we use 'book' to specify the Author -> Book reverse many-to-many hop):

反向引用不仅能应用到外键还可以应用到多对多关系例如我们可以请求每个作者注释该作者参与编辑的所有书的总页数注意我们是如何使用’boo’去指定Author -> Book反向多对多跳跃的

#~~~~~~~~~~

>>> Author.objects.annotate( total_pages=Sum('book__pages') )

#外键和关系都可以被反向引用反向引用时必须显式指出反向引用的表名的小写book。这里将Book模型的author多对多关系Author模型进行了反向,Author模型反向多对多关系Book模型的pages字段

#~~~~~~~~~~

结果查询集中的每个Author将会具有一个额外的属性称作total_pages。如果不指定这样的别名,将会是很长的’book__pages__sum)。

 

Or ask for the average rating of all the books written by author(s) we have on file:

或者请求我们在文件中存在的作者所写的所有书的平均评分:

#~~~~~~~~~~

Author.objects.annotate( avg_rating = Avg(‘book__rating’) )

Author.objects.aggregate( avg_rating = Avg(‘book__rating’) )

#~~~~~~~~~~

结果字典将具有一个称作’avg__rating’的键。如果不指定这样一个别名,将会是挺长的’book__rating__avg’。

 

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.

集合也可以参与到filters中。任何应用到正常模型字段上的filter()或exclude()将具有约束考虑使用集合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:

当与一个annotate()子句一起使用时,过滤器具有约束为其计算注释的对象的作用。例如,使用查询,你可以为标题以’Django’开头的所有书籍生成一个注释列。

#~~~~~~~

>>> from django.db.models import Avg, Count

>>> Book.objects.filter( name__startswith="Django" ).annotate( num_authors=Count('authors') )

#过滤返回一个查询集,总是可以为查询集增加注释列

#~~~~~~~

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:

当与一个集合aggregate()子句一起使用,过滤器具有约束为其计算集合的对象的作用。例如,使用查询,你可以为标题以’Django’开头的所有书籍生成平均单价。

#~~~~~~~~

Book.objects.filter( name__startswith=’Django’ ).aggregate( avg_price = Avg(price) )

#这里既然Book记录的price是一个single值,则Avg(price)是对查询集所有记录的price求均值。并返回单个字典avg_price:值。像下面:

#~~~~~~~

#~~~~~~~~

>>> from django.db.models import Avg, Max, Min

>>> Book.objects.aggregate( Avg('price'), Max('price'), Min('price') ) #Manager::aggregate()对全集计算集合意味的值返回这些值的字典

{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

#因为Book记录的price是一个FloatField()值字段所以Avg(‘price’)、Max(‘price’)、Min(‘price’)是对查询集所有记录的price进行集合处理并返回单个字典

#~~~~~~~~

#~~~~~~

>>> q = Book.objects.annotate( num_authors=Count('authors') )

>>> q[0].num_authors

2

>>> q[1].num_authors

1

#因为Book记录的authors是MantToManyField多对多字段,所以Count(‘authors’)是对Book记录的一对多authors字段的集合aggregate处理,对每个Book记录返回各自的注释列。

#~~~~~~

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.

被注释的值也可以被过滤注释列的别名可以用在filter()exclude()子句中以与其他模型字段相同的方式

For example, to generate a list of books that have more than one author, you can issue the query:

例如,生成具有两位及以上作者的书的列表,你可以这样处理查询:

#~~~~~~~~~

Books.objects.annotate( num_authors = Count(‘authors’) ).filter( num_authors__gte=2 )

Books.objects.annotate( num_authors = ExpressionWrapper( Count(‘authors’), output_field = IntegerField()) ).filter( num_authors__gte=2 )

#~~~~~~~~

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:

如果你需要两个注释,使用两个单独的过滤,你可以使用filter参数在任意继承中。例如,生成authors的一个列表,带每个作者较高评分(7星及以上的)的著作的计数:

#~~~~~~~~

>>> highly_rated = Count( 'book', filter=Q(book__rating__gte=7) )

>>> Author.objects.annotate( num_books=Count('book'), highly_rated_books=highly_rated )

#num_books是每个Author实例的著作数(反向关系'book'+Count集合),highly_rated_books是每个Author的著作中评分大于等于7分的著作计数(反向关系'book'+过滤+Count集合,封装查询条件的Q实例对象要放在其他实参’book’后面)

#上面Count()是构造的models.Count类实例对象,不是Manager类或QuerySet类的count()实例方法。

Help on class Count in module django.db.models.aggregates:

 

class Count(Aggregate)

 |  Count(*args, **kwargs)

 |  

 |  An SQL function call.

 |  

 |  Method resolution order:

 |      Count

 |      Aggregate

 |      django.db.models.expressions.Func

 |      django.db.models.expressions.SQLiteNumericMixin

 |      django.db.models.expressions.Expression

 |      django.db.models.expressions.BaseExpression

 |      django.db.models.expressions.Combinable

 |      builtins.object

 |  

 |  Methods defined here:

 |  

 |  __init__(self, expression, filter=None, **extra) #expression可以是要集合操作的字段,filter是查询子句

 |      Initialize self.  See help(type(self)) for accurate signature.

#~~~~~~~~

Choosing between filter and QuerySet.filter()在filter(Aggregate)类与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.

避免在单个注释annotation或集合aggregation中使用filter参数(下上面Count类的初始化函数中的filter实参)。使用QuerySet.filter()排除行会更高效。集合的filter参数只当对同一关系使用两个或更多不同条件的集合时才有用,下面Author反向关系引用Book(同一关系)的两个Count集合(两个),第二个需要对Count作出过滤(不同条件),。

Author.objects.annotate(

    num_books=Count('book'),

    highly_rated_books=Count( 'book', filter=Q(book__rating__gte=7)

)

 

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页