如何基于子查询获取计数

Question

尽管我一直在尝试基于网络搜索的查询，但我还是很难让查询正常工作，我想我需要一些帮助才能变得疯狂。

我有四种型号：

class Series(models.Model):
puzzles = models.ManyToManyField(Puzzle, through='SeriesElement', related_name='series')
...
class Puzzle(models.Model):
puzzles = models.ManyToManyField(Puzzle, through='SeriesElement', related_name='series')
...
class SeriesElement(models.Model):
puzzle = models.ForeignKey(Puzzle,on_delete=models.CASCADE,verbose_name='Puzzle',)
series = models.ForeignKey(Series,on_delete=models.CASCADE,verbose_name='Series',)
puzzle_index = models.PositiveIntegerField(verbose_name='Order',default=0,editable=True,)
class Play(models.Model):
puzzle = models.ForeignKey(Puzzle, on_delete=models.CASCADE, related_name='plays')
user = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True,null=True, on_delete=models.SET_NULL, related_name='plays')
series = models.ForeignKey(Series, blank=True, null=True, on_delete=models.SET_NULL, related_name='plays')
puzzle_completed = models.BooleanField(default=None, blank=False, null=False)
...

每个用户可以多次玩任何谜题，每次都创建一个Play记录。这意味着对于给定的CCD_ 2集合，我们可以具有多个CCD_，一些带有puzzle_completed = True，一些带有puzzle_completed = False

我试图(不成功(实现的是，通过注释计算每个系列的谜题nb_completed_by_user和nb_not_completed_by_user的数量。

对于nb_completed_by_user，我有一些在几乎所有情况下都有效的东西(我的一个测试中有一个小故障，到目前为止我还无法解释(：

Series.objects.annotate(nb_completed_by_user=Count('puzzles',
filter=Q(puzzles__plays__puzzle_completed=True, 
puzzles__plays__series_id=F('id'),puzzles__plays__user=user), distinct=True))

对于nb_not_completed_by_user，我可以在Puzzle上进行查询，得到正确的答案，但我无法将其转换为一个工作时不会出现错误的Subquery表达式，也无法获得一个Count表达式来给出正确的答案。

这个有效：

puzzles = Puzzle.objects.filter(~Q(plays__puzzle_completed=True,
plays__series_id=1, plays__user=user),series=s)

但是，当试图转移到子查询时，我找不到使用以下表达式不抛出错误的方法：ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

pzl_completed_by_user = Puzzle.objects.filter(plays__series_id=OuterRef('id')).exclude(
plays__puzzle_completed=True,plays__series_id=OuterRef('id'), plays__user=user)

下面的CCD_ 14表达式没有给我正确的结果：

Series.objects.annotate(nb_not_completed_by_user=Count('puzzles', filter=~Q(
puzzle__plays__puzzle_completed=True, puzzle__plays__series_id=F('id'), 
puzzle__plays__user=user))

有人能解释一下我是如何获得这两个值的吗？并最终向我提出一个链接，该链接清楚地解释了如何将子查询用于比官方文档中不太明显的情况

提前感谢

---

编辑2021年3月：我最近发现了两篇帖子，指导我找到了解决这个特定问题的一个潜在解决方案：Django Count和Sum注释相互干扰和Django 1.11注释子查询聚合

我从https://stackoverflow.com/users/188/matthew-schinckel和https://stackoverflow.com/users/1164966/benoit-blanchon具有帮助类：class SubqueryCount(Subquery)和class SubquerySum(Subquery)

class SubqueryCount(Subquery):
template = "(SELECT count(*) FROM (%(subquery)s) _count)"
output_field = PositiveIntegerField()

class SubquerySum(Subquery):
template = '(SELECT sum(_sum."%(column)s") FROM (%(subquery)s) _sum)'
def __init__(self, queryset, column, output_field=None, **extra):
if output_field is None:
output_field = queryset.model._meta.get_field(column)
super().__init__(queryset, output_field, column=column, **extra)

它工作得非常好！并且比传统的Django Count注释快得多。…至少在SQlite中是这样，可能还有其他人所说的PostgreSQL。

但当我在MariaDB环境中尝试时。。。它坠毁了！MariaDB显然不能/不愿意处理相关的子查询，因为这些子查询被认为是次优的。

在我的例子中，当我试图从数据库中同时为每个记录获得多个计数/不同的注释时，我确实看到了性能的巨大提高(在SQLite中(我想在MariaDB中复制。

有人能帮我找出一种方法来实现MariaDB的那些助手功能吗？

template在这种环境下应该是什么？

马修·辛克尔？伯努瓦·布兰钦？rktavi？

Answer 1

更深入地分析Django文档，我终于能够找到一种令人满意的方法来生成基于子查询的Count或Sum。

为了简化流程，我定义了以下辅助函数：

生成子查询：

def get_subquery(app_label, model_name, reference_to_model_object, filter_parameters={}):
"""
Return a subquery from a given model (work with both FK & M2M)
can add extra filter parameters as dictionary:
Use:
subquery = get_subquery(
app_label='puzzles', model_name='Puzzle',
reference_to_model_object='puzzle_family__target'
)
or directly:
qs.annotate(nb_puzzles=subquery_count(get_subquery(
'puzzles', 'Puzzle','puzzle_family__target')),)
"""
model = apps.get_model(app_label, model_name)
# we need to declare a local dictionary to prevent the external dictionary to be changed by the update method:
parameters = {f'{reference_to_model_object}__id': OuterRef('id')}
parameters.update(filter_parameters)
# putting '__id' instead of '_id' to work with both FK & M2M
return model.objects.filter(**parameters).order_by().values(f'{reference_to_model_object}__id')

计算通过get_subquery生成的子查询：

def subquery_count(subquery):
"""  
Use:
qs.annotate(nb_puzzles=subquery_count(get_subquery(
'puzzles', 'Puzzle','puzzle_family__target')),)
"""
return Coalesce(Subquery(subquery.annotate(count=Count('pk', distinct=True)).order_by().values('count'), output_field=PositiveIntegerField()), 0)

求和通过字段(user,series,puzzle)0上的get_subquery生成的子查询：

def subquery_sum(subquery, field_to_sum, output_field=None):
"""  
Use:
qs.annotate(total_points=subquery_sum(get_subquery(
'puzzles', 'Puzzle','puzzle_family__target'),'points'),)
"""
if output_field is None:
output_field = queryset.model._meta.get_field(column)
return Coalesce(Subquery(subquery.annotate(result=Sum(field_to_sum, output_field=output_field)).order_by().values('result'), output_field=output_field), 0)

所需进口：

from django.db.models import Count, Subquery, PositiveIntegerField, DecimalField, Sum
from django.db.models.functions import Coalesce

我花了很多时间来解决这个问题。。。我希望这将为你们中的许多人省去我在寻找正确的前进道路时所经历的所有挫折。