Django query, annotate a chain of related models

Question

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.

Why do you query with TopVideo, if the for loop simply "strips" the TopVideo context. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Nov 8, 2021 at 17:20

willeM_ Van Onsem · Accepted Answer · 2021-11-09 11:02:37Z

1

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.

edited Nov 9, 2021 at 11:02

answered Nov 8, 2021 at 17:26

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django query, annotate a chain of related models

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related