优化 Django 查询 – 减少数据库请求和正确的查询集访问

huangapple
go评论108阅读模式
英文:

Optimizing Django queries - reducing database requests & proper queryset access

问题

以下是您要翻译的内容：

我的视图

  1. def home(request):
  2.     q = ''
  3.     if request.GET.get('q'):
  4.         q = request.GET.get('q')
  5.     rooms = Room.objects.select_related('host', 'topic').prefetch_related('participants').filter(
  6.         Q(topic__name__icontains=q) |
  7.         Q(name__icontains=q) |
  8.         Q(description__icontains=q)
  9.     )
  10.     topics = Topic.objects.select_related('user')
  11.     profile = Profile.objects.filter(user=request.user)
  12.     r_messages = Message.objects.select_related('user', 'room').filter(Q(room__topic__name__icontains=q))
  13.     return render(request, 'base/home-page.html', context={
  14.         'rooms': rooms,
  15.         'topics': topics,
  16.         'profile': profile,
  17.         'r_messages': r_messages,
  18.     })

我的模型

  1. class Profile(models.Model):
  2.     user = models.OneToOneField(User, on_delete=models.CASCADE)
  3.     profile_image = models.FileField(default='default.jpg', upload_to='profile_image', null=True)
  4.     name = models.CharField(max_length=100, null=True, blank=True)
  5.     bio = models.TextField(max_length=5000, null=True, blank=True)
  6.     email = models.EmailField()
  7.     username = models.CharField(max_length=100)
  9.     def __str__(self):
  10.         return f'{self.user}'
  12.     def get_absolute_url(self):
  13.         return reverse('user-profile', args=[str(self.user)])
  15. class Topic(models.Model):
  16.     name = models.CharField(max_length=200)
  17.     user = models.ForeignKey(Profile, on_delete=models.CASCADE)
  19.     def __str__(self):
  20.         return f'{self.name}'
  22. class Room(models.Model):
  23.     host = models.ForeignKey(Profile, on_delete=models.SET_NULL, null=True)
  24.     topic = models.ForeignKey(Topic, on_delete=models.SET_NULL, null=True)
  25.     name = models.CharField(max_length=200)
  26.     description = models.TextField(null=True, blank=True)
  27.     participants = models.ManyToManyField(Profile, related_name='participants', blank=True)
  28.     updated = models.DateTimeField(auto_now=True)
  29.     created = models.DateTimeField(auto_now_add=True)
  31.     class Meta:
  32.         ordering = ['-updated', '-created']
  34.     def __str__(self):
  35.         return f'{self.name}'
  37. class Message(models.Model):
  38.     user = models.ForeignKey(Profile, on_delete=models.CASCADE)
  39.     room = models.ForeignKey(Room, on_delete=models.CASCADE)
  40.     body = models.TextField()
  41.     updated = models.DateTimeField(auto_now=True)
  42.     created = models.DateTimeField(auto_now_add=True)
  44.     def __str__(self):
  45.         return f'{self.body[:50]}'
  47.     class Meta:
  48.         ordering = ['-updated', '-created']

我的模板

  1. {% for room in rooms %}
  2.     <li>
  3.         {{ room.topic.host }}
  4.         <a href="{% url 'home' %}?q={{ room.topic.name }}">{{ room.topic.name }}<span>{{ room.topic.room_set.all.count }}</span></a>
  5.     </li>
  6. {% endfor %}

查询
当我尝试使用{{ room.topic.room_set.all.count }}时，如果有3个主题，ORM将查询3次DB以查找每个主题的房间计数。

我知道问题出现在模板中的此部分{{ room.topic.room_set.all.count }}，但不知道如何修复它。

英文:

My Views

  1. def home(request):
  2. q = &#39;&#39;
  3. if request.GET.get(&#39;q&#39;):
  4. q = request.GET.get(&#39;q&#39;)
  5. rooms = Room.objects.select_related(&#39;host&#39;, &#39;topic&#39;).prefetch_related(&#39;participants&#39;).filter(
  6. Q(topic__name__icontains=q) |
  7. Q(name__icontains=q) |
  8. Q(description__icontains=q)
  9. )
  10. topics = Topic.objects.select_related(&#39;user&#39;)
  11. profile = Profile.objects.filter(user=request.user)
  12. r_messages = Message.objects.select_related(&#39;user&#39;, &#39;room&#39;).filter(Q(room__topic__name__icontains=q))
  13. return render(request, &#39;base/home-page.html&#39;, context={
  14. &#39;rooms&#39;: rooms,
  15. &#39;topics&#39;: topics,
  16. &#39;profile&#39;: profile,
  17. &#39;r_messages&#39;: r_messages,
  18. })

My Models

  1. class Profile(models.Model):
  2. user = models.OneToOneField(User, on_delete=models.CASCADE)
  3. profile_image = models.FileField(default=&#39;default.jpg&#39;, upload_to=&#39;profile_image&#39;, null=True)
  4. name = models.CharField(max_length=100, null=True, blank=True)
  5. bio = models.TextField(max_length=5000, null=True, blank=True)
  6. email = models.EmailField()
  7. username = models.CharField(max_length=100)
  8. def __str__(self):
  9. return f&#39;{self.user}&#39;
  10. def get_absolute_url(self):
  11. return reverse(&#39;user-profile&#39;, args=[str(self.user)])
  12. class Topic(models.Model):
  13. name = models.CharField(max_length=200)
  14. user = models.ForeignKey(Profile, on_delete=models.CASCADE)
  15. def __str__(self):
  16. return f&#39;{self.name}&#39;
  17. class Room(models.Model):
  18. host = models.ForeignKey(Profile, on_delete=models.SET_NULL, null=True)
  19. topic = models.ForeignKey(Topic, on_delete=models.SET_NULL, null=True)
  20. name = models.CharField(max_length=200)
  21. description = models.TextField(null=True, blank=True)
  22. participants = models.ManyToManyField(Profile, related_name=&#39;participants&#39;, blank=True)
  23. updated = models.DateTimeField(auto_now=True)  # Save every time when we update
  24. created = models.DateTimeField(auto_now_add=True)  # Only save when room was created
  25. class Meta:
  26. ordering = [&#39;-updated&#39;, &#39;-created&#39;]
  27. def __str__(self):
  28. return f&#39;{self.name}&#39;
  29. class Message(models.Model):
  30. user = models.ForeignKey(Profile, on_delete=models.CASCADE)
  31. room = models.ForeignKey(Room, on_delete=models.CASCADE)
  32. body = models.TextField()
  33. updated = models.DateTimeField(auto_now=True)
  34. created = models.DateTimeField(auto_now_add=True)
  35. def __str__(self):
  36. return f&#39;{self.body[:50]}&#39;
  37. class Meta:
  38. ordering = [&#39;-updated&#39;, &#39;-created&#39;]

My Template

  1. {% for room in rooms %}
  2. &lt;li&gt;
  3. {{ room.topic.host }}
  4. &lt;a href=&quot;{% url &#39;home&#39; %}?q={{ room.topic.name}}&quot;&gt;{{ room.topic.name}}&lt;span&gt;{{ room.topic.room_set.all.count}}&lt;/span&gt;&lt;/a&gt;
  5. &lt;/li&gt;
  6. {% endfor %}

queries
When I try to use {{ room.topic.room_set.all.count}} and if there are 3 topics, the ORM will query 3 times to the DB to find each topic.rooms.count.

I know that the problem in this paro of code in template {{ room.topic.room_set.all.count}} but don't know how to fix it.

答案1

得分: 0

这里是翻译好的部分：

"it is very simple all you need is to use select_related and prefetch_related in your quires to get all the data you need at once so your modified query in your view will be look like this:

  1. def home(request):
  2.     ....
  3.     rooms = Room.objects.select_related('host', 'topic').prefetch_related('participants').filter(
  4.         Q(topic__name__icontains=q) |
  5.         Q(name__icontains=q) |
  6.         Q(description__icontains=q)
  7.     ) # this is a new query
  8.     topics = Topic.objects.select_related('user').prefetch_related('room_set') # this is a new query
  9.     profile = Profile.objects.filter(user=request.user)
  10.     r_messages = Message.objects.select_related('user', 'room').filter(Q(room__topic__name__icontains=q)) # this is a new query
  11.     ....

注意：我已经将原文中的 HTML 实体编码转换为普通字符。

英文:

it is very simple all you need is to use select_related and prefetch_related in your quires to get all the data you need at once so your modified query in your view will be look like this:

  1.  def home(request):
  2. ....
  3. rooms = Room.objects.select_related(&#39;host&#39;, &#39;topic&#39;).prefetch_related(&#39;participants&#39;).filter(
  4. Q(topic__name__icontains=q) |
  5. Q(name__icontains=q) |
  6. Q(description__icontains=q)
  7. ) # this is a new query
  8. topics = Topic.objects.select_related(&#39;user&#39;).prefetch_related(&#39;room_set&#39;) # this is a new query
  9. profile = Profile.objects.filter(user=request.user)
  10. r_messages = Message.objects.select_related(&#39;user&#39;, &#39;room&#39;).filter(Q(room__topic__name__icontains=q)) # this is a new query
  11. .....

huangapple
  • 本文由 发表于 2023年5月25日 01:58:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/76326271.html
  • database
  • django
  • django-queryset
  • python
  • templates
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定