When "select_related" is needed?

Question

In my project , Each candidate can takepart in some assessments,each assessment has some tests, each test has some questions in it and candidates should answer the questions at last scores of the questions are saved in question_score and test_score table

I need to get some values of field and use them

I write a method for question_result table, to get them

but i dont know if it is needed to use select_related or not

if it is needed how can i use it ?

Assessment:


class Assessment(BaseModel):
    company = models.ForeignKey(
        'company.Company',
        on_delete=models.CASCADE,
        related_name='assessments',
    )
    title = models.CharField(max_length=255)
    job_role = models.ForeignKey(
        JobRole,
        on_delete=models.PROTECT,
        related_name='assessments',
        blank=True,
        null=True,
    )
    tests = models.ManyToManyField(
        'exam.Test',
        related_name='assessments',
        blank=True,
        through='TestOfAssessment',
    )
    candidates = models.ManyToManyField(
        'user.User',
        related_name='taken_assessments',
        blank=True,
        through='candidate.Candidate'
    )
  

    def __str__(self):
        return self.title

Test:


class Test(BaseModel):
    class DifficultyLevel(models.IntegerChoices):
        EASY = 1
        MEDIUM = 2
        HARD = 3

    company = models.ForeignKey(
        'company.Company',
        on_delete=models.PROTECT,
        related_name='tests',
        null=True,
        blank=True,
    )

    questions = models.ManyToManyField(
        'question.Question',
        related_name='tests',
        blank=True,
        help_text='Standard tests could have multiple questions.',
    )
    level = models.IntegerField(default=1, choices=DifficultyLevel.choices)
   
    title = models.CharField(max_length=255)
    summary = models.TextField()
  
   
    def __str__(self):
        return self.title

Question :


class Question(BaseModel):
  
    company = models.ForeignKey(
        'company.Company',
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='company_questions',
    )
    question_text = models.TextField()

   def __str__(self):
        return truncatewords(self.question_text, 7)

TestResult:

class TestResult(BaseModel):
 
    candidate = models.ForeignKey(
        'Candidate',
        on_delete=models.CASCADE,
        related_name='test_results',
    )

    test = models.ForeignKey(
        'exam.Test',
        on_delete=models.CASCADE,
    )

    test_score = models.DecimalField(default=0.00, max_digits=5, decimal_places=2)

    def __str__(self):
        return f'{self.candidate.user.email} - {self.test.title}'

Candidate :


class Candidate(BaseModel):

    assessment = models.ForeignKey(
        'assessment.Assessment',
        on_delete=models.CASCADE,
    )
    user = models.ForeignKey(
        'user.User',
        on_delete=models.CASCADE,
    )

    is_rejected = models.BooleanField(default=False)

    def __str__(self):
        return f'{self.user.email} - {self.assessment.title}'

Company :

class Company(models.Model):

    manager = models.ForeignKey('user.User', on_delete=models.CASCADE, related_name='user_companies')
    name = models.CharField(max_length=255)
    city = models.ForeignKey('company.City', null=True, on_delete=models.SET_NULL)
    address = models.CharField(max_length=255, blank=True, null=True)


    def __str__(self):
        return self.name

QuestionResult :


class QuestionResult(BaseModel):

    test = models.ForeignKey(
        'TestResult',
        on_delete=models.CASCADE,
        related_name='question_results',
    )
    question = models.ForeignKey(
        'question.Question',
        on_delete=models.CASCADE,
        related_name='results',
    )

    result = models.TextField(
        null=True,
        blank=True,
    )

    answer_score = models.DecimalField(default=0.00, max_digits=5, decimal_places=2)

    def __str__(self):
        return f'{self.test.candidate.user.email} - {self.question}'



  def text_variables(self):
       
        email = self.test.candidate.user.email
        company_name = self.test.test.company.name
        assessment_name = self.test.candidate.assessment.title
        candidate_first_name = self.test.candidate.user.first_name
        job_name = self.test.candidate.assessment.job_role
        user_fullname = User.full_name
        data = dict(
            job_name=job_name,
            company_name=company_name,
            email=email,
            assessment_name=assessment_name,
            candidate_first_name=candidate_first_name,
            job_name=job_name,
            user_fullname = user_fullname
        )
        return data

I wrote the def text_variables(self): method to fill the data dictionary and use it somewhere else

it work properly but i dont know if it needed to use selected_related or not

something like this (it does not work)

    def text_variables(self):

        question_result_object = QuestionResult.objects.filter(id=self.id).select_related(
         "test__candidate","test__test__company","test__candidate__assessment")
         
        email = question_result_object.test.candidate.user.email
        company_name = question_result_object.test.test.company.name
        assessment_name = question_result_object.test.candidate.assessment.title
        candidate_first_name = question_result_object.test.candidate.user.first_name
        job_name = question_result_object.test.candidate.assessment.job_role
         data = dict(
            job_name=job_name,
            company_name=company_name,
            email=email,
            assessment_name=assessment_name,
            candidate_first_name=candidate_first_name,
            job_name=job_name,
            user_fullname = user_fullname
        )
        return data

the error is :

  File "E:\work\puzzlelity\talent-backend\candidate\models.py", line 385, in report_to_candidate_email_text_variables
    email = question_result_object.test.candidate.user.email
AttributeError: 'QuerySet' object has no attribute 'test'
[03/Jan/2023 17:59:00] "POST /api/v1/candidatures/183f8432-ea81-4099-b211-3b0e6475ffab/submit-answer/ HTTP/1.1" 500 123319

I dont know how should i use the select_related

nigel222 · Accepted Answer · 2023-01-03T17:25:47.860

It's never required. It optimizes querysets, especially in ListViews.

Consider your Assessment model. It has ForeignKey fields company and job_role. If you simply fetch

assessment = Assessment.objects.get( id=something)

and then refer to assessment.company, that causes a second DB query to fetch the company object. And then a third if you refer to assessment.job_role.

You can reduce these three queries to one by using

assessment = Assessment.objects.select_related(
    'company', 'job_role') .get( id=something)

which does a more complex query to retrieve all the data.

Where it matters is in a list view where you iterate over a large number of assessment objects in Python or in a template. For example, if object_list is assessment.objects.all() and there are 300 of them, then

{% for assessment in object_list %}
     ... stuff ...
     {{assessment.company.name }}
     ...
{% endfor %}

Will hit the DB 300 times, once for each company! If you use select_related, all 300 companies linked to the 300 assessments will be retrieved in a single DB query. which will be very noticeably faster.

I'd strongly recommend installing Django Debug Toolbar in your development project. Then click on the SQL option on any view, and you can see what SQL was required, and in particular how many SQL queries were performed and whether there were batches of repetetive queries which mean there's a trivial optimisation to be made.

When "select_related" is needed?

1 Answers1