英文:
Django Group By Aggregation works until additional fields added
问题
我想在给定的赛道上对以下赛道变体进行分组，以便在索引页面上每个赛道只有一个条目。在随后的赛道详细页面上，我将显示各种配置。
如果我保持查询简单如下，它就能正常工作。
track_listing = (Tracks.objects
.values('sku', 'track_name')
.annotate(variations=Count('sku'))
.order_by()
)
然而，添加其他字段如track_id或location会破坏或改变分组。
track_listing = (Tracks.objects
.values('sku', 'track_name', 'track_id')
.annotate(variations=Count('sku'))
.order_by()
)
有没有一种方法可以在包含其他字段的同时保持分组。location对于每一行并不是唯一的，而track_id的一个示例允许我检索赛道图像。
英文:
I'd like to group the following track variations at the given circuit so on an index page I only have a single entry for each circuit. On a subsequent circuit detail page I will show the various configurations.
If I keep the query simple as below it works.
track_listing = (Tracks.objects
.values('sku', 'track_name')
.annotate(variations=Count('sku'))
.order_by()
)
However adding other fields such as track_id or location breaks or changes the grouping.
track_listing = (Tracks.objects
.values('sku', 'track_name', 'track_id')
.annotate(variations=Count('sku'))
.order_by()
)
Is there a way to keep the group while including other fields. The location is not unique to each row and having one example of track_id allows me to retrieve a track image.
答案1
得分: 0
请使用子查询来实现。
from django.db.models import Subquery, OuterRef
subquery = Tracks.objects.filter(
sku=OuterRef('sku'),
track_name=OuterRef('track_name')
).values('track_id')[:1]
track_listing = (Tracks.objects
.annotate(track_id=Subquery(subquery))
.values('sku', 'track_name', 'track_id')
.annotate(variations=Count('sku'))
.order_by()
.distinct('sku', 'track_name')
)
英文:
Please use subquery for that.
from django.db.models import Subquery, OuterRef
subquery = Tracks.objects.filter(
sku=OuterRef('sku'),
track_name=OuterRef('track_name')
).values('track_id')[:1]
track_listing = (Tracks.objects
.annotate(track_id=Subquery(subquery))
.values('sku', 'track_name', 'track_id')
.annotate(variations=Count('sku'))
.order_by()
.distinct('sku', 'track_name')
)
答案2
得分: 0
解决此问题的一种方法是按 sku 和 track_name 字段对结果进行分组，然后使用单独的查询来检索每个轨道变体的 track_id 和其他详细信息，如下所示：
# 第一个查询以检索轨道变体
track_variations = (Tracks.objects
.values('sku', 'track_name')
.annotate(variations=Count('sku'))
.order_by())
# 遍历轨道变体并检索每个变体的附加详细信息
for track in track_variations:
# 第二个查询以检索每个轨道变体的详细信息
track_details = Tracks.objects.filter(sku=track['sku'], track_name=track['track_name']).values('track_id', 'location', 'other_fields')
track['variations'] = list(track_details)
英文:
One way to resolve this issue is to group the results by the sku and track_name fields, and use a separate query to retrieve the track_id and other details for each track variation, like the following:
# First query to retrieve the track variations
track_variations = (Tracks.objects
.values('sku', 'track_name')
.annotate(variations=Count('sku'))
.order_by())
# Loop through the track variations and retrieve additional details for each variation
for track in track_variations:
# Second query to retrieve details for each track variation
track_details = Tracks.objects.filter(sku=track['sku'], track_name=track['track_name']).values('track_id', 'location', 'other_fields')
track['variations'] = list(track_details)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论