Filter on relation field in Django ORM

Refresh

February 2019

Views

29 time

0

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?

1 answers

0

Использование Prefetchвы можете получить соответствующие объекты для соотношения (для многих ко многим и реверс иностранных ключевых отношений) с использованием предварительно фильтруется QuerySet:

queryset = queryset.filter(
               Q(usermedia__user__id__in=my_matches) & ~Q(usermedia__user=user)
            )

prefetch = UserMedia.objects.filter(user_id__in=my_matches).exclude(user=user)
queryset = queryset.prefetch_related(
    Prefetch('usermedia_set', prefetch, to_attr='filtered_usermedia')
)

for el in queryset:
    for usermedia in el.filtered_usermedia:
        # iterate over the filtered usermedia
        # without any additional queries
        calculate_something(usermedia)                    

Обратите внимание, что это приведет к еще одного запроса для предварительной выборки всех связанных объектов (так в общей сложности два запросов, независимо от того, сколько строк вы запрашиваете), по сравнению с одним дополнительным запросом перед для каждого объекта в главном QuerySet.