用于temp的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. 哪个对象应该传递给上下文变量中的模板，以便在视图中而不是模板中执行逻辑？你知道吗

我知道的问题是：

# 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中，在这种sql查询中转换的qs用户：

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

视图和模板可以如下所示（您甚至不需要将上下文传递给模板，所有内容都将取自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 %}

我不得不添加自定义模板过滤器来获得attr by choice id（很可能我错过了更优雅的解决方案）：

# 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)