I have a model Media that has a relationship to a model UserMedia (user ratings). Also there's a model called UserMatchScore (match scores of users) that is relevant to the question.
In a view I am querying the Media table, in this view there's an option to only get the media that my matches have rated, but I haven't rated. Also an average of ratings is returned based on the subset of me and my matches, not all users that have rated the media. I do this with annotation.
What I do is filter the Media table for elements that I haven't rated, but my matches have rated, this is simple, but it only does half of the job. All media is returned that I haven't rated, but my matches have rated, but the relational field UserMedia still contains all ratings, this one isn't filtered, so there's no way to calculate the average of ratings for the subset of my matches. Here's the query I am describing:
queryset = models.Media.objects queryset = queryset.filter( Q(usermedia__user__id__in=my_matches) & ~Q(usermedia__user=user) )
The only way to get to the expected result is to loop through the queryset and filter each element of the UserMedia relation, but this is too slow, so has to be done with a DB query.
for el in queryset: el.usermedia_set.filter(~Q(user=user)).filter(user=my_matches)
Does anyone know how to do this with Django ORM?