Django: Filtering via SQL, not Python

Question

I created the following context variables context["genders"] and context["ages"]. Currently, there is a lot of work done by Python under #Filtering, while I think it would be better done in #Query. However, that's where I currently struggle. Do you have an idea on how to achieve the pre-filtering in the #Query section via SQL?

Please not the int(answer_obj.answer) as answer is a 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

models.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")

No, it works as expected, I am just trying to reduce the work for Python and move it into the SQL query part. I am basically trying to extend that part here `responses = Response.objects.filter` so I can "drop" the Python #Filtering — Joey Coder, Nov 12 '19 at 08:52
Does this answer your question? [possible to filter the queryset after querying? django](https://stackoverflow.com/questions/45228187/possible-to-filter-the-queryset-after-querying-django) — shaik moeed, Nov 12 '19 at 09:05
Partly. However, as I understood that would improve the Python filtering, but not bring the logic into the SQL query. — Joey Coder, Nov 12 '19 at 09:11
So why can't you do that? You already know how to d filter across relationships, so what's the problem? — Daniel Roseman, Nov 12 '19 at 09:15

mfrackowiak · Answer 1 · 2019-11-12T09:44:22.920

0

Filtering It looks to me, that you want all responses, where answer is higher than eight for question focused on friend recommendation. Is it expected that you might have the same response appended to the filtered responses more than once, or will there be only one question of this type? I think you could rewrite it as follows:

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

And populating the context:

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)

This should allow you to avoid firing the queries to iterate over response.answers.all(), and hopefully achieve the same result.

edited Nov 12 '19 at 09:44

answered Nov 12 '19 at 09:15

mfrackowiak

1,294
8
11

Not sure why you need the annotation here. Why not just `.filter(..., answers__answer__gt=8)`? – Daniel Roseman Nov 12 '19 at 09:19
To be honest I don't have django at hand to test now, and while I feel *should* work without annotation for this case, I am bit worried about comparing straight to text field, where with higher numbers it potentially _could_ result in e.g. "9" being greater than "81" – mfrackowiak Nov 12 '19 at 09:22
The first sentence summary brings it to the point @mfrackowiak. Daniel Roseman I think that wouldn't work because it first must be "transformed" to an integer field? "Is it expected that you might have the same response appended to the filtered responses more than once, or will there be only one question of this type?" > Each response should only be once appended. – Joey Coder Nov 12 '19 at 09:23
@JoeyCoder it should be then fine with this query, it's just in your code you were appending in the nested loop the variable from the outer loop, so just in theory if there were more questions fulfilling the if I could imagine duplicates in the filtered_responses list. – mfrackowiak Nov 12 '19 at 09:39
Okay understood. I somehow get an error when applying the code above: `django.db.utils.ProgrammingError: operator does not exist: text > integer LINE 1: ...ndation_to_friends' AND "surveys_answer"."answer" > 8) ORDER... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.` – Joey Coder Nov 12 '19 at 09:40
I updated the query to use Cast, https://stackoverflow.com/questions/28101580/how-do-i-cast-char-to-integer-while-querying-in-django-orm https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#cast which probably should be used from the beginning. – mfrackowiak Nov 12 '19 at 09:51

Django: Filtering via SQL, not Python

1 Answers1