Django newbie question: get count of associated ForeignKey, conditional on field within the ForeignKey table?

Question

Hi all

Newbie question. I have Django models that look like this:

class Video(models.Model):
    uploaded_by = models.ForeignKey('VideoUser') 
    problem_video = models.BooleanField(default=False)
class VideoUser(models.Model):
    name = models.CharField(max_length=250 )
    def num_videos(self):
        num_videos = Video.objects.filter(uploaded_by=self, problem_video=false).count()
        return num_videos

And I want to be able to produce a leaderboard of users who've uploaded at least one non-problem video, ordered by the number of non-problem videos they've uploaded.

I can get part of the way there with annotate - I can produce an ordered list, but I don't know how to make it conditional on the problem_video field being False.

 leaderboard = VideoUser.objects. \
            .annotate(num_submissions=Count('videosubmission')) \
            .filter(num_submissions__gt=0).order_by('-num_submissions')

Can anyone suggest how to extend this to take the problem_video field into account too? Maybe I need a bridging table...?

Answer 1

If I remember correctly,

leaderboard = VideoUser.objects. \
        .annotate(num_submissions=Count('videosubmission')) \
        .filter(num_submissions__gt=0, video__problem_video=False).order_by('-num_submissions')