单个查询中的两个注释 [英] Two annotations in a single query
问题描述
我正在尝试构建一个查询集来进行计数,该查询集涉及三个模型.
class Owner(models.Model):name = models.CharField(max_length=10, null=False)
class Location(models.Model):name = models.CharField(max_length=10, null=False)owner = models.ForeignKey(Owner, on_delete=models.SET_NULL, null=True, blank=True)
类资产(models.Model):name = models.CharField(max_length=10, null=false)owner = models.ForeignKey(Owner, on_delete=models.SET_NULL, null=True, blank=True)location = models.ForeignKey(Location, on_delete=models.SET_NULL, null=True, blank=True)
我正在尝试对所有者的位置和所有者的资产进行计数,我可以将其作为两个单独的查询集实现,如下所示:
locations = Owner.objects.all().annotate(locations=Count('location'))
assets = Owner.objects.all().annotate(assets=Count('asset'))
这很好用,但我想要做的是为两个值获取一行并构建一个类似于下面的表.
<代码>|业主 |资产 |地点 ||--------+--------+-----------||所有者1 |10 |3 ||业主2 |100 |20 ||业主3 |70 |50 |
我试图将两个注释放在一个查询中,但我似乎没有得到正确的结果,资产和位置的计数要么相同,要么我得到非常大的数字,我认为这是因为两者都计数操作相互影响.
下面的查询为我提供了相同的资产和位置数字
queryset = Owner.objects.all().annotate(assets=Count('asset'),locations=Count('location'))
或
下面的查询为我提供了大量的资产和位置数据
queryset = Owner.objects.all().annotate(assets=Count('asset')).annotate(locations=Count('location'))
我可以直接用 SQL 做到这一点,但我希望不要走这条路.
谢谢 Nara,我尝试了你建议的不同排列,最终奏效.
queryset = Owner.objects.all().annotate(assets=Count('asset', distinct=True), location=Count('location', distinct=True))
>
这是 Django shell 中的样子.
<预><代码>>>>从库存.模型导入 *>>>从 django.db.models 导入计数>>>queryset = Owner.objects.all().annotate(assets=Count('asset', distinct=True), location=Count('location', distinct=True))>>>变量(查询集 [0]){'_state': <django.db.models.base.ModelState object at 0x0427B230>, 'id': 1, 'name': 'Owner 1', 'assets': 5, 'locations': 4}>>>变量(查询集 [1]){'_state': <django.db.models.base.ModelState object at 0x0427B2D0>, 'id': 2, 'name': 'Owner 2', 'assets': 3, 'locations': 4}>>>变量(查询集 [2]){'_state': <django.db.models.base.ModelState object at 0x0427B4D0>, 'id': 3, 'name': 'Owner 3', 'assets': 2, 'locations': 6}如您所见,这同时为我提供了资产和位置计数.
I am trying to build a query set to do a count, the query set involves three models.
class Owner(models.Model):
name = models.CharField(max_length=10, null=False)
class Location(models.Model):
name = models.CharField(max_length=10, null=False)
owner = models.ForeignKey(Owner, on_delete=models.SET_NULL, null=True, blank=True)
class Asset(models.Model):
name = models.CharField(max_length=10, null=false)
owner = models.ForeignKey(Owner, on_delete=models.SET_NULL, null=True, blank=True)
location = models.ForeignKey(Location, on_delete=models.SET_NULL, null=True, blank=True)
I am trying to do a count for Locations for the owner and Assets for the owner, I can achieve this as two separate Querysets as follows:
locations = Owner.objects.all().annotate(locations=Count('location'))
assets = Owner.objects.all().annotate(assets=Count('asset'))
This works fine, but what I'm trying to do is get a single row for both values and build a table similar to the one below.
| Owner | Assets | Locations |
|--------+--------+-----------|
| owner1 | 10 | 3 |
| owner2 | 100 | 20 |
| owner3 | 70 | 50 |
I've tried to put both annotations in a single query but I don't appear to get the right results, the count is either the same for both assets and location or I get very large numbers which I assume because both count operations are impacting each other.
Query below gives me the same numbers for both asset and location
queryset = Owner.objects.all().annotate(assets=Count('asset'), locations=Count('location'))
or
Query below gives me large numbers for both asset and location
queryset = Owner.objects.all().annotate(assets=Count('asset')).annotate(locations=Count('location'))
I can do this directly with SQL but I'm hoping not do go down that path.
Thanks Nara, I tried a different permutation of what you suggested which ended up working.
queryset = Owner.objects.all().annotate(assets=Count('asset', distinct=True), locations=Count('location', distinct=True))
Here is what it looks like from a Django shell.
>>> from inventory.models import *
>>> from django.db.models import Count
>>> queryset = Owner.objects.all().annotate(assets=Count('asset', distinct=True), locations=Count('location', distinct=True))
>>> vars(queryset[0])
{'_state': <django.db.models.base.ModelState object at 0x0427B230>, 'id': 1, 'name': 'Owner 1', 'assets': 5, 'locations': 4}
>>> vars(queryset[1])
{'_state': <django.db.models.base.ModelState object at 0x0427B2D0>, 'id': 2, 'name': 'Owner 2', 'assets': 3, 'locations': 4}
>>> vars(queryset[2])
{'_state': <django.db.models.base.ModelState object at 0x0427B4D0>, 'id': 3, 'name': 'Owner 3', 'assets': 2, 'locations': 6}
As you can see that gives me both the asset and the location counts.
这篇关于单个查询中的两个注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!