如何正确执行多字段的Django查询

Question

为了学习，我正在做一个IMDB Clone项目。它有两个重要的模式：电影和名人。你知道吗

电影模型有三个MTM字段，都与名人模型有关。你知道吗

class Movie(models.Model):
    # .. unrelated fields deleted for code brevity ..

    directors = models.ManyToManyField(celeb_models.Celebrity, related_name='movies_as_director', 
                    limit_choices_to=Q(duties__name__icontains='Director'))    
    writers = models.ManyToManyField(to=celeb_models.Celebrity, related_name='movies_as_writer', 
                    limit_choices_to=Q(duties__name__icontains='Writer'))
    casts = models.ManyToManyField(to=celeb_models.Celebrity, through='MovieCast')

我想删除所有三个字段，只添加一个MTM字段。你知道吗

class Movie(models.Model):       
    # one 'crews' field takes the place of three fields ('directors', 'writers', 'casts')
    # but it shows bad query performance.
    crews = models.ManyToManyField(celeb_models.Celebrity, through='MovieCrew', related_name='movies')

并创建了一个中间模型，其中包含一些方法和一个自定义管理器（应该可以发挥作用）。你知道吗

class MovieCrewManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset()

    def get_directors(self):
        qs = self.get_queryset()
        return qs.filter(duty__name__icontains='Director').select_related('crew')

    def get_writers(self):
        qs = self.get_queryset()
        return qs.filter(duty__name__icontains='Writer').select_related('crew')

    def get_casts(self):
        qs = self.get_queryset()
        return qs.filter(duty__name__icontains='Cast').select_related('crew')


class MovieCrew(models.Model):
    movie = models.ForeignKey(Movie, on_delete=models.CASCADE, related_name='movie_crews')  #Movie Model
    duty = models.ForeignKey(celeb_models.Duty, default=1, on_delete=models.CASCADE)
    crew = models.ForeignKey(celeb_models.Celebrity, on_delete=models.CASCADE)  # Celebrity Model
    role = models.CharField(max_length=75, default='', blank=True,  
                    help_text='e.g. short story, scrrenplay for writer, voice for cast')
    screen_name = models.CharField(max_length=75, default='', blank=True,
                    help_text="crew's name on movie")

    objects = MovieCrewManager()

    def clean(self, *args, **kwargs):
        if not self.duty in self.crew.duties.all():
            raise ValidationError('crew duty and selected duty should match', code='invalid')
        super(MovieCrew, self).clean(*args, **kwargs)

    def save(self, *args, **kwargs):
        self.full_clean()
        super(MovieCrew, self).save(*args, **kwargs)

    def __str__(self):
        return self.crew.full_name

减少字段计数的原因是期望更好的性能。因为我调用了三个不同的查询来查询所有三个mtm，这三个mtm确实属于同一个模型（名人）。但是，目前我保留了所有四个字段，因为我无法获得预期的查询性能。你知道吗

有四个页面（视图）用一个列表模板列出电影。我也创造了他们的对应物。因此，我有一堆糟糕的和一堆表现良好的页面来看看它们之间的区别。你知道吗

其中一种观点表现良好（三个电影领域：演员、导演、编剧）：

class MovieListMixin2(ListView):
    queryset = movie_model.objects.prefetch_related(
        'writers', 'casts', 'directors', 'genres', 'comments')
    template_name = 'movies/index2.html' 
    paginate_by = pagination


class IndexView2(MovieListMixin2):
    ordering = ('-release_year', 'title')

    def get_context_data(self, **kwargs):
        context = super(IndexView2, self).get_context_data(**kwargs)
        context['title'] = '(GQ) Latest movies'
        context['title_suffix'] = 'by release date'
        return context

及其模板（为简洁起见，简称）：

{% for movie in object_list %}
[...]
<p class="small"><strong>Directors:</strong> 
    {% for director in movie.directors.all %}
        <a href="{% url 'celebs:celeb_detail' director.id director.slug %}">{{ director.full_name }}</a>, 
    {% endfor %}            
</p>
<p class="small"><strong>Writers:</strong> 
    {% for writer in movie.writers.all %}
        <a href="{% url 'celebs:celeb_detail' writer.id writer.slug %}">{{ writer.full_name }}</a>, 
    {% endfor %}            
</p>                
<p class="small"><strong>Stars:</strong> 
    {% for cast in movie.casts.all %}
        <a href="{% url 'celebs:celeb_detail' cast.id cast.slug %}">{{ cast.full_name }}</a>,  
    {% endfor %}            
</p>  
[...]
{% endfor %}

和同一视角的对应者，但这是表现不好的（一个电影领域：剧组）：

class MovieListMixin(ListView):
    queryset = movie_model.objects.prefetch_related('movie_crews', 'genres', 'comments')  
    template_name = 'movies/index.html' 
    paginate_by = pagination


class IndexView(MovieListMixin):
    ordering = ('-release_year', 'title')

    def get_context_data(self, **kwargs):
        context = super(IndexView, self).get_context_data(**kwargs)
        context['title'] = 'Latest movies'
        context['title_suffix'] = 'by release date'
        return context

以及它的模板（为了简洁而缩短）：

{% for movie in object_list %}
[...]
<p class="small"><strong>Directors:</strong> 
    {% for director in movie.movie_crews.get_directors %}
        <a href="{% url 'celebs:celeb_detail' director.crew.id director.crew.slug %}">{{ director.crew.full_name }}</a>, 
    {% endfor %}            
</p>
<p class="small"><strong>Writers:</strong> 
    {% for writer in movie.movie_crews.get_writers %}
        <a href="{% url 'celebs:celeb_detail' writer.crew.id writer.crew.slug %}">{{ writer.crew.full_name }}</a>, 
    {% endfor %}            
</p>                
<p class="small"><strong>Stars:</strong> 
    {% for cast in movie.movie_crews.get_casts %}
        <a href="{% url 'celebs:celeb_detail' cast.crew.id cast.crew.slug %}">{{ cast.crew.full_name }}</a>,  
    {% endfor %}            
</p>        
[...]    
{% endfor %}

我没有从两个实现中得到任何错误。另一方面

性能良好的页面在2.42毫秒内只有9个查询执行。你知道吗

SELECT ••• FROM "movies_movie"
SELECT ••• FROM "django_session" WHERE ("django_session"."expire_date" > '''2019-10-03 02:20:13.197659''' AND "django_session"."session_key" = '''bftca58feksf1cbo17qzgc40l24eb893''')
SELECT ••• FROM "users_user" WHERE "users_user"."id" = '1'
SELECT ••• FROM "movies_movie" ORDER BY "movies_movie"."release_year" DESC, "movies_movie"."title" ASC LIMIT 5
SELECT ••• FROM "celebs_celebrity" INNER JOIN "movies_movie_writers" ON ("celebs_celebrity"."id" = "movies_movie_writers"."celebrity_id") WHERE "movies_movie_writers"."movie_id" IN ('8', '3', '7', '9', '6') ORDER BY "celebs_celebrity"."last_name" ASC, "celebs_celebrity"."first_name" ASC
SELECT ••• FROM "celebs_celebrity" INNER JOIN "movies_moviecast" ON ("celebs_celebrity"."id" = "movies_moviecast"."cast_id") WHERE "movies_moviecast"."movie_id" IN ('8', '3', '7', '9', '6') ORDER BY "celebs_celebrity"."last_name" ASC, "celebs_celebrity"."first_name" ASC
SELECT ••• FROM "celebs_celebrity" INNER JOIN "movies_movie_directors" ON ("celebs_celebrity"."id" = "movies_movie_directors"."celebrity_id") WHERE "movies_movie_directors"."movie_id" IN ('8', '3', '7', '9', '6') ORDER BY "celebs_celebrity"."last_name" ASC, "celebs_celebrity"."first_name" ASC
SELECT ••• FROM "movies_genre" INNER JOIN "movies_movie_genres" ON ("movies_genre"."id" = "movies_movie_genres"."genre_id") WHERE "movies_movie_genres"."movie_id" IN ('8', '3', '7', '9', '6') ORDER BY "movies_genre"."name" ASC
SELECT ••• FROM "reviews_moviecomment" WHERE "reviews_moviecomment"."movie_id" IN ('8', '3', '7', '9', '6')

性能不佳的页面在5.65毫秒内执行了22个查询。你知道吗

SELECT ••• FROM "movies_movie"  
SELECT ••• FROM "django_session" WHERE ("django_session"."expire_date" > '''2019-10-03 02:54:13.177499''' AND "django_session"."session_key" = '''bftca58feksf1cbo17qzgc40l24eb893''')
SELECT ••• FROM "users_user" WHERE "users_user"."id" = '1'
SELECT ••• FROM "movies_movie" ORDER BY "movies_movie"."release_year" DESC, "movies_movie"."title" ASC LIMIT 5
SELECT ••• FROM "movies_moviecrew" WHERE "movies_moviecrew"."movie_id" IN ('8', '3', '7', '9', '6')
SELECT ••• FROM "movies_genre" INNER JOIN "movies_movie_genres" ON ("movies_genre"."id" = "movies_movie_genres"."genre_id") WHERE "movies_movie_genres"."movie_id" IN ('8', '3', '7', '9', '6') ORDER BY "movies_genre"."name" ASC
SELECT ••• FROM "reviews_moviecomment" WHERE "reviews_moviecomment"."movie_id" IN ('8', '3', '7', '9', '6')
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '8' AND "celebs_duty"."name" LIKE '''%Director%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '8' AND "celebs_duty"."name" LIKE '''%Writer%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '8' AND "celebs_duty"."name" LIKE '''%Cast%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '3' AND "celebs_duty"."name" LIKE '''%Director%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '3' AND "celebs_duty"."name" LIKE '''%Writer%''' ESCAPE '\')
  15 similar queries.       
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '3' AND "celebs_duty"."name" LIKE '''%Cast%''' ESCAPE '\')
  15 similar queries.       
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '7' AND "celebs_duty"."name" LIKE '''%Director%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '7' AND "celebs_duty"."name" LIKE '''%Writer%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '7' AND "celebs_duty"."name" LIKE '''%Cast%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '9' AND "celebs_duty"."name" LIKE '''%Director%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '9' AND "celebs_duty"."name" LIKE '''%Writer%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '9' AND "celebs_duty"."name" LIKE '''%Cast%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '6' AND "celebs_duty"."name" LIKE '''%Director%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '6' AND "celebs_duty"."name" LIKE '''%Writer%''' ESCAPE '\')
  15 similar queries.   
SELECT ••• FROM "movies_moviecrew" INNER JOIN "celebs_duty" ON ("movies_moviecrew"."duty_id" = "celebs_duty"."id") INNER JOIN "celebs_celebrity" ON ("movies_moviecrew"."crew_id" = "celebs_celebrity"."id") WHERE ("movies_moviecrew"."movie_id" = '6' AND "celebs_duty"."name" LIKE '''%Cast%''' ESCAPE '\')
  15 similar queries.

所以，我需要你的帮助。你知道吗

1. 把三个MTM字段减少到一个MTM字段好吗？

2. 如果是这样，为什么它的表现如此糟糕？为什么模型管理器的方法会创建重复的查询？

谢谢你。你知道吗

PS：这个项目是开源的，它作为一个存储库托管在github上。如果你想看到活的代码，你可以克隆或下载它。你知道吗

它有初始数据要加载。因此，在几分钟内就可以很容易地使它工作。数据库是sqlite3。你知道吗

如果您以后阅读本文并想查看代码，我将其作为一个分支添加。我希望我不会改变它。https://github.com/pydatageek/imdb-clone/tree/query_comparison_1

Answer 1

这里的问题是，每次你做filter，就像你在所有MovieCrewManager方法中做的那样，它总是返回数据库-绕过prefetch_related优化。你知道吗

我会用不同的方法来处理这个问题。因为您需要所有的数据，并且要提前获取这些数据，所以可以在Movie上编写方法来使用该缓存（如果存在），并用Python进行过滤。比如：

class Movie(models.Model):       
    def _get_crew(self, duty_name):
        if hasattr(self, '_prefetched_objects_cache') and 'movie_crews' in self._prefetched_objects_cache:
            return [c for c in self._prefetched_objects_cache['movie_crews'] if c.duty.name == duty_name]
        else:
            return self.movie_crews.filter(duty__name=duty_name)

    @property
    def directors(self):
      return self._get_crew('Director')

    @property
    def writers(self):
      return self._get_crew('Writer')

    @property
    def cast(self):
      return self._get_crew('Cast')

那么您的视图查询集可以是：

queryset = movie_model.objects.prefetch_related('movie_crews__duty', 'movie_crews__crew, 'genres', 'comments')  

模板变成：

{% for director in movie.directors %}
    <a href="{% url 'celebs:celeb_detail' director.crew.id director.crew.slug %}">{{ director.crew.full_name }}</a>, 
{% endfor %}
...

在我的测试中，这将查询减少到7个。你知道吗