Group data by

Using Django Using the ORM
1

Hi,

I have a function that returns projectName, QuestionnaireTitle and finalScore The function loops through every project and every questionnaire.

I’m trying to format the data so that i end up with a list or a dict that contains the projectName and the scores for each questionnaire so that i can pass this into a JSChart.

The format of the chart is:

			   name: 'Project Name',
			   data: [5,8,4,7,2,5,5,7]

My current queryset is returning

[{'q_rounded': 100, 'title': 'Critical Fundamentals', 'final_score': 5.0, 'project': <Project: ProjectName>},...]

Is there a way to take the existing queryset and construct the data in the way i need, or should is the a better way within the function?

for project in all_projects:
        for q in questionnaires:
            print("getting ", q.title, "responses for", project.name, project.id)
            if ProjectQuestionnaireResponse.objects.filter(project_name_id=project.id, questionnaire_id = q.id).exists():    
                q_response = ProjectQuestionnaireResponse.objects.get(project_name_id=project.id, questionnaire_id = q.id)
                q_answered = ProjectQuestionnaireAnswer.objects.filter(response = q_response, answer__isnull=False).count()
                if q_answered > 1:
                    q_count = (100 / ProjectQuestionnaireQuestion.objects.filter(questionnaire_id = q.id).count() * q_answered)
                else:
                    q_count = 0
                q_rounded = (5 * round(q_count / 5))
                # Scoring Calulator #
                # Get Questionnaire Range
                total_questions =ProjectQuestionnaireQuestion.objects.filter(questionnaire_id = q.id).count()
                score_range = (4 * total_questions)             
                green = 2
                red = -2
                green_answer_value = (ProjectQuestionnaireAnswer.objects.filter(response = q_response,answer__choice_value="Green").count() * green)
                red_answer_value = (ProjectQuestionnaireAnswer.objects.filter(response = q_response,answer__choice_value="Red").count() * red)                               

                total_score = (green_answer_value + red_answer_value               
                if total_score is not None:              
                    final_score = round(((2 * total_questions + total_score) / score_range) * 10,2)
                else:
                    pass        
                results.append({'q_rounded':q_rounded,'title':q.title,'final_score':final_score,"project":project})            
            else:
                q_rounded = 0           
   
                results.append({'q_rounded':q_rounded,'title':q.title,"project":project})

Thanks

2

You’re going to execute “number of projects” * “number of questionnaires” * 6 queries on this view. You’ll see a lot of improvement by working on appropriate uses of annotations and aggregations in this view to reduce the number of queries being executed here.

You may also find it worthwhile to “prefetch” and “cache” some data to avoid making excess queries.

It’s not going to be easy, or necessarily obvious, but it will be the best way of improving the performance here.

3

I have been looking at annotate and aggregate. I struggle to understand the documentation and how I transpose that to what I should be doing

Is it that I should be replacing the get and filters statements with a single annotate statement or something along those lines?

Or the for loops

I will experiment to see what produces what.

Tom.

4

You’ll still be using your filters for identifying what is being retrieved. You’d be adding annotations and/or aggregations (along with some query expressions) to perform the calculations within the context of the query.

Just as one quick example, you have:

q_response = ProjectQuestionnaireResponse.objects.get(project_name_id=project.id, questionnaire_id = q.id)
q_answered = ProjectQuestionnaireAnswer.objects.filter(response = q_response, answer__isnull=False).count()

You can reduce these two queries to one: (I hope I get this close to right - caution, I’m winging this)

q_response = ProjectQuestionnaireResponse.objects.annotate(
answered=Count('projectquestionnaireanswer', filter=Q(projectquestionnaireanswer__answer__isnull=False)
).get(project_name_id=project.id, questionnaire_id = q.id)
5

Thanks for this Ken.