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

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.

CodePudding user response：

You could do it in two database calls - first by getting a list of IDs of questions answered by the user

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

Then get a list of questions not in that list

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

I haven't tested the next one, but you may be able to use the related_name to exclude it in one call.

question_list = Questions.objects.exclude(answerer__contains=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,

question_list = Question.objects.exclude(pk__in=questions_answered).order_by('?')[:10]