文章/答案/技术大牛

发布

社区首页 >问答首页 >如何基于子查询获取计数？

问如何基于子查询获取计数？
EN

Stack Overflow用户

提问于 2020-05-08 11:05:37

回答 1查看 460关注 0票数 0

我正在痛苦地让一个查询工作，尽管我一直在尝试基于我的网络搜索，我认为我需要一些帮助，然后才变得疯狂。

我有四种型号：

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记录。这意味着对于给定的一组(user,series,puzzle)，我们可以有几个Play记录，一些用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)

下面的Count表达式没有给出正确的结果：

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 and Sum annotations interfere with each other和Django 1.11 Annotating a Subquery Aggregate。

我使用帮助类实现了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计数注释要快得多。..。至少在SQlite中，也可能像其他人所说的PostgreSQL中一样。

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

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

有人能帮我找出一种方法来实现MariaDB的辅助函数吗？

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

马修-辛克尔？贝诺特-布兰肯？rktavi？

django

django-queryset

correlated-subquery

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-03-13 14:35:52

再深入一点，再详细分析Django文档，我终于能够产生一种令人满意的基于子查询的计数或求和方法。

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

若要生成子查询：

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)

将通过get_subquery在字段field_to_sum上生成的子查询之和

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

我花了很多时间来解决这个问题..。我希望这将使你们中的许多人省去我所经历的所有挫折，找到正确的方法进行下去。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/61677702

复制

相似问题

问如何基于子查询获取计数？
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何基于子查询获取计数？EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何基于子查询获取计数？
EN