How to retrieve attributes from a foreign key related model to another foreign key related model?

Question

I'm using django 4.1.2 with python 3.10.8. I have three models one for user management, one for questions and another for answers. They are described below:

class User(AbstractUser):
    phone_number = models.CharField(max_length=14, unique=True)
    first_name = models.CharField(max_length=40)
    father_name = models.CharField(max_length=40)
    email = models.EmailField(unique=True, required=True)
    age = models.CharField(max_length=3)
    username = models.CharField(max_length=8, required=True)

class Question(models.Model):
    question = models.CharField(
        max_length=500,
        null=False,
        unique=True
    )
    creating_staff = models.ForeignKey(
        User,
        null=False,
        on_delete=models.PROTECT,
        to_field="phone_number",
    )
    options = models.JSONField(null=False)
    correct_option = models.CharField(max_length=250, null=False)
    question_ts = models.DateTimeField(auto_now_add=True, null=False)

    class Meta:
        verbose_name = "Question"

    def __str__(self) -> str:
        return f"{self.question}"


class Answer(models.Model):
    answer = models.CharField(max_length=500, null=False)
    question_answered = models.ForeignKey(
        Question,
        null=False,
        on_delete=models.PROTECT,
        related_name="question_answered"
    )
    answering_user = models.ForeignKey(
        User,
        null=False,
        on_delete=models.PROTECT,
        to_field="phone_number",
        related_name="answerer"
    )
    status = models.BooleanField(null=False)
    answer_ts = models.DateTimeField(auto_now_add=True, null=False)

    class Meta:
        verbose_name = "Answer"

    def __str__(self) -> str:
        return f"{self.answer} -- {self.answering_user}"

This is the urls.py file:

from django.urls import path

from .views import (AnswerView)

app_name = "commons"

urlpatterns = [
    path("play/", AnswerView.as_view(), name="play"),
]

What I'm trying to do is whenever a user has logged in a wants to answer a set of questions by going to /commons/play/, on the GET request I want to parse out all the previous questions that user has answered and always display new questions by randomly selecting 10 out of the unanswered questions.

What I've done thus far is:

import random
from django.shortcuts import (redirect, render)
from django.views import View
from django.contrib import messages
from django.contrib.auth.mixins import LoginRequiredMixin

from .models import (Question, Answer, User)

class AnswerView(LoginRequiredMixin, View):

    def get(self, request):
            answerer = request.user
            total_answers_by_user = Answer.objects.filter(answering_user=answerer)
            questions = Question.objects.all()
            question_list = list()
            for ans in total_answers_by_user:
                for q in questions:
                    if not ans.question_answered == q:
                        question_list.append(q)
            questions_count = question.count()
            try:
                rand_sample = random.sample(range(questions_count), 10)
            except (ValueError, Exception) as e:
                print(f"{e} population for random sample < 10, perhaps new player")
                total_questions = Question.objects.all().count()
                rand_sample = random.sample(range(total_questions), 10)
                questions_to_ask = Question.objects.filter(id__in=rand_sample)
            else:
                questions_to_ask = Question.objects.filter(id__in=rand_sample)

            return render(request, "commons/answer.html", {"questions": questions_to_ask})

But I'm very doubtful that this is an efficient way of retrieving unanswered or new questions, especially when it comes to multiple users. Is there a better way to retrieve all the questions that the user has previously answered and only display new or unanswered questions?

My gratitude before hand for your response.

Answer 1

To break down what you need to do, first you want a list of IDs of questions answered by the user

answered_qs = (Answer.objects
    .filter(answering_user=answerer)
    .values_list('question_answered_id', flat=True)
    )

Then you want to exclude that list of questions from the set of all questions

question_list = Question.objects.exclude(pk__in=answered_qs)

Using related_names, it is possible to do this all in one call. Here we follow questions_answered to the set of answers where the field answering_user is our requester, and exclude that set.

questions_list = Question.objects.exclude(question_answered__answering_user=answerer)

With a slight performance hit if there are a lot of questions, you can even get your random set of 10 in the same database call, eg,

questions_list = (
    Question.objects
    .exclude(question_answered__answering_user=answerer)
    .order_by('?')[:10]
    )