python - 模板的 Django/SQL 复式表

Question

我的 Django 应用程序中有以下模型:

from django.contrib.auth.models import User

class Poll(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User)

class Choice(models.Model):
    poll = models.ForeignKey(Poll, on_delete=models.CASCADE)
    text = models.CharField(max_length=200)

class Vote(models.Model):
    choice = models.ForeignKey(Choice)
    user = models.ForeignKey(User)

对于给定的投票，我需要在模板中显示如下所示的复式表，列出在此投票中投票的所有用户，并为用户投票的每个选项放置一个“x”:

       [choice1] [choice2] [choice3]
[user1]                        x
[user2]    x                   x
[user3]              x          
[user4]              x         x

1. 使用 django ORM 实现此目的的最佳方法是什么，以便在填充表时最大限度地减少数据库命中？
2. 哪个对象应该在上下文变量中传递给模板，以便在 View 而不是模板中执行逻辑？

我知道的查询:

# get all votes for the given Poll (pk)
votes = Vote.objects.filter(choice__poll__pk=pk)
# get all users that has voted
usernames = votes.values('user__username')
# get the choices for the Poll
choices = Poll.objects.get(pk=pk).choice_set.all()

Answer 1

我能够通过以下方式解决问题:

# yourapp/models.py
from django.utils.functional import cached_property
from django.db.models import Case, When, BooleanField

class Poll(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User)

    @cached_property
    def choices(self):
        return self.choice_set.order_by('id')

    @cached_property
    def users_choices(self):
        users = User.objects.filter(vote__choice__poll=self)

        for choice  in self.choices:
            users = users.annotate(
                **{str(choice.id): Case(When(vote__choice_id=choice.id, then=True), output_field=BooleanField())}
            )

        return users

例如在 PostgreSQL 用户 qs 在这样的 sql 查询中转换:

SELECT
  "yourapp_user"."id",
  "yourapp_user"."username",
  CASE WHEN "yourapp_vote"."choice_id" = 1
    THEN TRUE
  ELSE NULL END AS "1",
  CASE WHEN "yourapp_vote"."choice_id" = 2
    THEN TRUE
  ELSE NULL END AS "2",
  CASE WHEN "yourapp_vote"."choice_id" = 3
    THEN TRUE
  ELSE NULL END AS "3"
FROM "yourapp_user"
  LEFT OUTER JOIN "yourapp_vote" ON ("yourapp_user"."id" = "yourapp_vote"."user_id")
  LEFT OUTER JOIN "yourapp_choice" ON ("yourapp_vote"."choice_id" = "yourapp_choice"."id")
WHERE "yourapp_choice"."poll_id" = 1

View 和模板可以如下所示(您甚至不需要将上下文传递给模板，一切都将从 Poll 模型属性中获取):

# yourapp/views.py
class PollDetailView(DetailView):
    model = Poll
    template_name = 'user_choices.html'


# yourapp/templates/user_choices.html
{% extends 'base.html' %}{% load customtags %}

{% block content %}
    <h1>{{ poll.title }}</h1>
    <table border="1">
        <tr>
            <th>username</th>
            {% for choice in poll.choices %}<th>choice{{ choice.id }}</th>{% endfor %}
        </tr>
        {% for user_choice in poll.users_choices %}
        <tr>
            <td>{{ user_choice.username }}</td>
            {% for choice in poll.choices %}
                <td>{% if user_choice|get_attr:choice.id %}+{% endif %}</td>
            {% endfor %}
        </tr>
        {% endfor %}
    </table>
{% endblock %}

我不得不添加自定义模板过滤器以通过选择 id 获取 attr(可能有更优雅的解决方案，但我错过了):

# yourapp/templatetags/customtags.py
from django import template

register = template.Library()


@register.filter(name='get_attr')
def get_attr(obj, attr_name):
    return getattr(obj, str(attr_name), None)