注释Mptt模型的下降总数 [英] Annotate Total Count of Descents of Mptt Model

问题描述

给出以下模型，我想获取所有页面的查询集，并用与该页面相关联的线程中的注释总数(包括与页面相关联的注释线程树中的所有注释)进行注释.

Given the models below, I want to get a queryset of all pages, annotated with the total number of comments in the thread associated to the page, including all comments in a comment thread tree associated with pages.

我正在使用 django-mptt 存储评论树.

I am using django-mptt to store comment tree.

我可以使用 comment.get_descendant_count()在python中获得此功能，但这在查询所有页面时效率很低

I can get this in python using comment.get_descendant_count(), but this is very ineficient when querying all pages

class CommentThread(models.Model):
    ...


class Page(models.Model):
    ...
    thread = models.ForeignKey("CommentThread", ...)   



class Comment(MPTTModel):
    body = models.TextField()
    author = models.ForeignKey("User", ...)

    # Validation ensures a comment has
    # a thread or parent comment but not both
    thread = models.ForeignKey("CommentThread", related_name="comments", ...)
    parent = TreeForeignKey(
                'self',
                on_delete=models.CASCADE,
                null=True,
                blank=True,
                related_name='children'
    )

    class MPTTMeta:
        level_attr = 'mptt_level'
        order_insertion_by=['name']

此模型使我可以向页面添加多个根注释"，而且还可以将注释作为递归方式嵌套在每个注释下.

This model allows me to add multiple "root comments" to page, but also nest comments under each comment as replies, recursively.

# Model Use Examples

thread = CommentThread()
page = Page(thread=thread)

# add page level root comments
comment1 = Comment(thread=thread, ...)
comment2 = Comment(thread=thread, ...)
comment3 = Comment(thread=thread, ...)

# add Comment Replies to comment #1
comment_reply1 = Comment(parent=comment1, ...)
comment_reply2 = Comment(parent=comment1, ...)
comment_reply3 = Comment(parent=comment1, ...)

当前方法-在python中

有效，但效率很低:

Current approach - in python

Works but very inneficient:

page = Page.objects.first()
total_comments = [c.get_descendant_count() for c in page.thread.comments.all()]

我尝试过的

我不确定如何使用查询集和注释来实现这一点.我知道每个mptt模型也都有一个 treed_id ，所以我想我需要构建一个更复杂的子查询.

What I have tried

I am not sure how to achieve this with querysets and annotations. I know each mptt model also get a treed_id, so I am guessing I would need to build a more complex subquery.

要仅获取根注释的数量(不包括嵌套注释)，我可以这样做:

To get the number of root comments only (not including nested), I could do it like this:

pages = Page.objects.all().annotate(num_comments=models.Count("thread__comments"))
num_root_comments = pages[0].num_comments

再一次，我们的目标是获取所有注释，包括嵌套的注释:

Once again, the goal is to get all comments, including nested:

# Non working code - this kind of  pseudo queryset code of what I am trying:

all_page_comments = Comment.objects.filter(tree_id__in= (Page.thread__comments__tree_id))
Page.objects.all().annotate(num_comments=Count(Subquery(all_page_comments))

在此先感谢您提供的任何帮助.

Thanks in advance for any help provided.

感谢以下@andrey的回答，找到了可行的解决方案.不确定它是否最佳，但似乎在单个查询中返回正确的值.

Got a working solution thanks to @andrey's answer below. Not sure it's optimal but seems to return the correct values in a single query.

threads = CommentThread.objects.filter(
        id=models.OuterRef("thread")
    ).annotate(
        comment_count=models.Sum(
            Floor((models.F("comments__rght") - models.F("comments__lft") - 1) / 2)
        )
    )

qs_pages_with_comment_count = (
    Page.objects
    .annotate(
        comment_count=models.Subquery(
            threads.values("comment_count")[:1], output_field=models.IntegerField()
        )
    )
    # Count from subquery included count of descendents of 
    # each "root" comment but not the root comment itself
    # so we add  number of root comments per thread on top
    .annotate(
        comment_count=models.F("comment_count")
        + models.Count("thread__comments", distinct=True)
    )
)

推荐答案

queryset.annotate(
    descendants_count=Floor((F('rght') - F('lft') - 1) / 2)
).values(
    'descendants_count'
).aggregate(
    total_count=Count('descendants_count')
)

让我解释一下

首先， get_descendant_count 的当前方法仅操作现有数据，因此我们可以在Queryset中使用它.

First, current method of get_descendant_count just operates existing data, so we can use it in Queryset.

def get_descendant_count(self):
    """
    Returns the number of descendants this model instance has.
    """
    if self._mpttfield('right') is None:
        # node not saved yet
        return 0
    else:
        return (self._mpttfield('right') - self._mpttfield('left') - 1) // 2

这是当前mptt模型的方法.在queryset中，我们确保所有实例都已保存，因此我们将跳过该实例.

This is the current mptt models' method. In queryset we are sure that all of instances is already saved so we'll skip that.

下一步是将数学运算转换为db表达式.在Django 3.0中，出现了 Floor 表达式.但是我们甚至可以在1.7中使用它(就像我一样)

Next step is to transform math operations into db expressions. In Django 3.0 appeared Floor expression. But we can use it even in 1.7 (as I do)

from django.db.models.lookups import Transform

class Floor(Transform):
     function = 'FLOOR'
     lookup_name = 'floor'

如果需要，可以重构它以使用 self._mpttfield('right')模拟而不是硬编码的 rght，lft ，并将其作为 Manager 方法

If you want you can refactor this to use self._mpttfield('right') analog instead of hardcoded rght, lftand make this as Manager method

测试.我的后代拥有最重要的元素

Let's test. I have top element with descendants

In [1]: m = MenuItem.objects.get(id=settings.TOP_MENU_ID)

In [2]: m.get_descendant_count()
Out[2]: 226

In [3]: n = m.get_descendants()

In [4]: n.annotate(descendants_count=Floor((F('rght') - F('lft') - 1) / 2)).values('descendants_count').aggregate(total_count=Count('descendants_count'))
Out[4]: {'total_count': 226}

这篇关于注释Mptt模型的下降总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！