How do i find "Average Word Count of Comments for Each Blog"?

This is my models:

from django.db import models
from django.conf import settings
from django.utils import timezone

class Blog(models.Model):
    author = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    title = models.CharField(max_length=200)
    text = models.TextField()
    created_date = models.DateTimeField(default=timezone.now)
    published_date = models.DateTimeField(blank=True, null=True)

    def publish(self):
        self.published_date = timezone.now()
        self.save()

    def __str__(self):
        return f"{self.title}"

class Comment(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='comments')
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE, related_name='comments')
    comment = models.TextField()
    published_date = models.DateTimeField(default=timezone.now)

    def __str__(self):
        return f"{self.comment}"

                             

I tried annotate() using Blog.objects.annotate(avg_comment_length = Avg(Length(comments__comment))) but Length() gets the total characters in a comment, not word count. How can I modify this to calculate the average word count of comments for each blog?

This is not easy, partly because there is no clear definition what a word exactly is.

A "clever" way to calculate the number of words, is calculate the number of spaces, and then use that, so with:

from django.db.models import Value

Blog.objects.annotate(
    avg_comment_length=Avg(
        Length('comments__comment')
        - Length(Replace('comments__comment', Value(' '), Value('')))
        + 1
    )
)
Вернуться на верх