Django：通过SQL而不是Python进行过滤

Question

我创建了以下上下文变量context["genders"]和context["ages"]。 目前，Python在#Filtering下做了很多工作，而我认为最好在#Query中完成。 然而，这正是我目前努力的方向。关于如何通过SQL实现#Query部分的预过滤，您有什么想法吗？你知道吗

请不要使用int(answer_obj.answer)，因为answer是TextField。你知道吗

# Query
responses = Response.objects.filter(
    survey__event=12, survey__template=settings.SURVEY_POST_EVENT
).order_by("-created")

# Filtering
filtered_responses = []
for response in responses:
    for answer_obj in response.answers.all():
        if (
            answer_obj.question.focus == QuestionFocus.RECOMMENDATION_TO_FRIENDS
            and int(answer_obj.answer) >= 8
        ):
            filtered_responses.append(response)


# Context
gender_list = []
age_list = []
for response in filtered_responses:
    for answer_obj in response.answers.all():
        # Here a list of all the genders that gave that answer:
        if answer_obj.question.focus == QuestionFocus.GENDER:
            gender_list.append(answer_obj.answer)

        # Here a list of all the ages that gave that answer:
        if answer_obj.question.focus == QuestionFocus.AGE:
            age_list.append(answer_obj.answer)

context["genders"] = gender_list
context["ages"] = age_list

你知道吗型号.py你知道吗

class Answer(TimeStampedModel):
    question = models.ForeignKey(
        "surveys.Question", on_delete=models.CASCADE, related_name="answers"
    )
    response = models.ForeignKey(
        "Response", on_delete=models.CASCADE, related_name="answers"
    )
    answer = models.TextField(verbose_name=_("Answer"))
    choices = models.ManyToManyField(
        "surveys.AnswerOption", related_name="answers", blank=True
    )

class Response(TimeStampedModel):
    class Language(Choices):
        CHOICES = settings.LANGUAGES

    survey = models.ForeignKey(
        "surveys.Survey", on_delete=models.CASCADE, related_name="responses"
    )
    order = models.ForeignKey(
        "orders.Order",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="response",
    )
    attendee = models.ForeignKey(
        "attendees.Attendee",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="response",
    )
    total_time = models.PositiveIntegerField(
        null=True, blank=True, verbose_name=_("Total time")
    )
    ip_address = models.GenericIPAddressField(null=True, verbose_name=_("IP Address"))
    language = models.CharField(
        max_length=Language.get_max_length(),
        choices=Language.CHOICES,
        verbose_name=_("Language"),
    )

class Question(TimeStampedModel):
    survey = models.ForeignKey(
        "surveys.Survey", on_delete=models.CASCADE, related_name="questions"
    )
    question_set = models.ForeignKey(
        "QuestionSet", on_delete=models.CASCADE, related_name="questions"
    )
    title = models.CharField(max_length=100, verbose_name=_("Title"))
    help_text = models.TextField(null=True, blank=True, verbose_name=_("Help text"))
    type = models.CharField(
        max_length=QuestionType.get_max_length(),
        choices=QuestionType.CHOICES,
        verbose_name=_("Question type"),
    )
    focus = models.CharField(
        max_length=QuestionFocus.get_max_length(),
        choices=QuestionFocus.CHOICES,
        verbose_name=_("Question focus"),
    )
    required = models.BooleanField(default=False, verbose_name=_("Is required?"))
    position = models.PositiveSmallIntegerField(
        null=True, blank=True, verbose_name=_("Position")
    )

    # Translatable fields
    i18n = TranslationField(fields=("title", "help_text"))

    class Meta:
        ordering = ("position", "pk")

Answer 1

过滤 在我看来，你们想要的是所有的答案，其中答案是高于8的问题集中在朋友推荐。您是否希望将同一个响应多次附加到筛选后的响应中，或者只有一个此类问题？我想你可以重写如下：

filtered_response = responses.filter(
    answers__question__focus=QuestionFocus.RECOMMENDATION_TO_FRIENDS
).annotate(
    answer_num=Cast("answers__answer", IntegerField()),
).filter(
    answer_num__gt=8,
)

以及填充上下文：

context["genders"] = Answer.objects.filter(
    response_id__in=filtered_response.values_list("id", flat=True),
    question__focus=QuestionFocus.GENDER,
).values_list("answer", flat=True)
context["ages"] = Answer.objects.filter(
    response_id__in=filtered_response.values_list("id", flat=True),
    question__focus=QuestionFocus.AGE,
).values_list("answer", flat=True)

这应该允许您避免触发查询来迭代response.answers.all()，并希望获得相同的结果。你知道吗