Django query, annotate a chain of related models-CodePudding

I have following schema with PostgreSQL.

class Video(models.Model):
    title = models.CharField(max_length=255)
    created_at = models.DateTimeField()
    disabled = models.BooleanField(default=False)
    view_count = DecimalField(max_digits=10, decimal_places=0)

class TopVideo(models.Model):
    videos = (Video, on_delete=models.CASCADE, primary_key=True)

class Comment(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    video = models.ForeignKey(Video, related_name="comments", on_delete=models.CASCADE)

The reason I have a TopVideo model is because I have millions of videos and querying them takes a long time on a cheap server, so I have a secondary model that is populated by a celery task, and flushes and re-populates on each run, which makes the homepage load time much faster. The task runs the query that you see next, and saves them into the TopVideo model. This way, the task may take long to run, but user doesn't have to wait for the expensive query anymore.

Before having the TopVideo model, I ran this query for my homepage:

     videos = (
         Video.objects.filter(created_at__range=[start, end])
         .annotate(comment_count=Count("comments"))
         .exclude(disabled=True)
         .order_by("-view_count")[:100]
     )

This worked perfectly and I had access to "comment_count" in my template, where I could easily show the number of comments each video had.

But now that I make this query:

top_videos = (
    TopVideo.objects.all().annotate(comment_count=Count("video__comments"))
        .select_related("video")
        .order_by("-video__view_count")[:100]
)

and with a simple for-loop,

videos = []
for video in top_videos:
    videos.append(video.video)

I send the videos to the template to render. My problem is, I no longer have access to the "comment_count" inside the template, and naturally so; I don't send the queryset anymore. How can I now access the comment_count?

Things I tried:

Sending the TopVideo query to template did not work. They're a bunch of TopVideo objects, not Video objects.
I added this piece of code in my template "{{ video.comments.count }}" but this makes 100 requests to the database, which is not really optimal.

CodePudding user response：

You can set the .comment_count to your Video objects with:

videos = []
for top_video in top_videos:
    video = top_video.video
    video.comment_count = top_video.comment_count
    videos.append(video)

but that being said, it is unclear to my why you are querying with TopVideo if you basically strip the TopVideo context from the video.

If you want to obtain the Videos for which there exists a TopVideo object, you can work with:

videos = Video.objects.filter(
    created_at__range=[start, end], topvideo__isnull=False
).annotate(
    comment_count=Count('comments')
).exclude(disabled=True).order_by('-view_count')[:100]

The topvideo__isnull=False will thus filter out Videos that are not TopVideos.