Loop through queryset and group items

Using Django Templates & Frontend
1

Hi,

I have a model which holds the scores for answers to questions within questionnaries.

I’d like to group the scores for each project into a list of dictionaries i think. So that i can create some charts.

I’m running scores = ProjectScores.objects.filter(profile__user=profile.user) to return all values.

And then I’m doing:

   scores_across_all_projects = []
    for score in scores:
        if score.score:
          scores_across_all_projects.append(score.score)

But this is returning just the set of scores [23,45,11,...] Ideally i’d like to be ProjectName:ProjectName,"scores: 24,45,11

class ProjectScores(models.Model):
    project = models.ForeignKey(Project, on_delete=models.CASCADE, blank=True) 
    profile = models.ForeignKey(Profile, on_delete=models.CASCADE, blank=True)
    questionnaire = models.ForeignKey('ProjectQuestionnaire', on_delete=models.CASCADE, blank=True)
    score = models.SmallIntegerField(blank=True, null=True)

    def __str__(self):
        return str(self.project)
    
    class Meta:
        verbose_name = "Project Score"

Thanks

2

For clarity:

For all instances of ProjectScores where profile is for a single profile, you want a list of scores by project in a list.

You would like the desired output to look something like this:

[{'name': 'Project 1', 'scores': [24,45,11]},
 {'name': 'Project 2', 'scores': [33,55,27]},
 ...
]

Is this correct?

3

Yep that’s exactly what I’m after. Thanks

4

Ok, so then the easiest way is to loop over each project and get a list of scores for that project.

First, a side note. You have:

You’re referencing the user here where you don’t need to. This would do the same thing:
scores = ProjectScores.objects.filter(profile=profile)

Now, for what you’re really trying to get to, it’s going to be something along the lines of:

projects = Project.objects.filter(projectscores__profile=profile)
score_list = [
  {'name': a_project.project_name,
   'scores': ProjectScores.objects.filter(project=a_project, profile=profile).values_list('score', flat=True)
  }
  for a_project in projects
]

Now, if you’re doing this on PostgreSQL, you can get a little fancier with the PostgreSQL-specific ArrayAgg function:

project_scores = 
Project.objects.filter(projectscores__profile=profile).annotate(
  name = name,
  scores = ArrayAgg('projectscores__score')
).values('name', 'scores')

(Standard caveat here - I’m winging this, so this may not be 100% correct as written. There may need to be some “fine tuning” done to get the exact results you’re looking for.)

5

Thanks, Ken. It’s producing the list of dictionaries, but it’s repeating for each project.

[{'name': 'Project_a', 'scores': <QuerySet [35, 47]>}, {'name': 'Project_a', 'scores': <QuerySet [35, 47]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 100, 99, 60, 100, 95, 79]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 
100, 99, 60, 100, 95, 79]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 100, 99, 60, 100, 95, 79]>}, {'name': 'Project_c', 'scores': <QuerySet [49, 72]>}...,

Do i need to loop through the scores_list?

6

What does your implementation of your solution look like? (What is the code you’re using for the query?)

What query function exists to prevent duplicate results being returned?

7

Im running this:

    score_list = [
        {'name': a_project.name,
        'scores': ProjectScores.objects.filter(project=a_project, profile=profile).values_list('score', flat=True)
    }
        for a_project in projects
    ]
    scores = Project.objects.filter(projectscores__profile=profile)   
    
    print(score_list)

But within my model i have the updates being executed using create or update each time a questionnaire is completed.

Each line in the DB is the project name and the score for that questionnaire. So for each project, it will have 7 entries. 1 for each questionnaire.

            project_score, created = ProjectScores.objects.get_or_create(project = project, questionnaire = response.questionnaire, profile = profile, score = ov)
            project_score.save()

Is this the wrong approach?

8

I’m not following what you’re trying to ask here.

Your previous question is identifying that you’re getting multiple copies of the same project in your query results.

There is a specific query function that exists to ensure you only get unique results for a query. What is that function?

Second, I don’t see how you can get the results you describe if you’re running the query I provided. You’re showing that scores has querysets assigned to it, but the query I provided would not do that.

9

Sorry, Ken. I misread your reply.

The output of

    score_list = [
        {'name': a_project.name,
        'scores': ProjectScores.objects.filter(project=a_project, profile=profile).values_list('score', flat=True)
    }
        for a_project in projects
    ]

is producing:

[{'name': 'Project_a', 'scores': <QuerySet [35, 47]>}, {'name': 'Project_a', 'scores': <QuerySet [35, 47]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 100, 99, 60, 100, 95, 79]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 
100, 99, 60, 100, 95, 79]>}, {'name': 'Project_b', 'scores': <QuerySet [4, 100, 99, 60, 100, 95, 79]>}, {'name': 'Project_c', 'scores': <QuerySet [49, 72]>}...,

I should be using .distinct() i think?

10

On the projects query, Correct!

11

Right so that is good with limiting the results to unique projects, but the result is

[{'name': 'Project A', 'scores': <QuerySet [35, 47]>}...

The scores are within another queryset. I assume because the scores are being retrieved using

ProjectScores.objects.filter(project=a_project, profile=profile).values_list('score', flat=True)

which creates a nested queryset? How do I get the final output:

[{'name': 'Project A', 'scores': [35, 47]}...

Sorry, Ken. I feel I should know this by now :frowning:

12

Don’t forget that what you’re looking at in the shell (or in a print statement) is not the internal format of the data. It’s a human-readable representation. The two are not necessarily the same.

The question is, what are you going to do with these results now that you have them? (How are you going to use them?)

13

My plan is to pass the data to Java script charts.

I’ve tried using for each within the template, but I cant get to the actual scores.

Are you saying that why I have got, is enough for me to pass use within my template?

14

Yes, you’ve got everything you need.

15

Ok, great. I’ll see if I can figure it out…

I’ll be back :wink:

16

Can i ask for a hint?

17

Why don’t you post what you’ve defined in your view for your context and include what you’ve tried in your template and we’ll work forward from there.

18

Hi Ken,

This is what i have:

    projects = Project.objects.filter(projectscores__profile=profile).distinct() 
    scores = [
        {'name': a_project.name,
        'scores': ProjectScores.objects.filter(project=a_project, profile=profile).values_list('score', flat=True)
    }
        for a_project in projects
    ]


    return render(request, 'app/index.html',{"total_projects":total_projects,"all_projects":all_projects,"profile":profile,'questionnaires':questionnaires,'scores':scores})

Im my template i done:

{% for project in scores %}
{{ project.name }}
{% endfor %}

This works for the names being passed to the charts. So I assume i’m needing to do something with scores in the view to extract the values into a separate variable to be past into context?

19

Ok, composite choices for the names of some of these entities may be adding to the confusion.

You have:

and

At this point in your template, scores is the list of dicts being passed into the template.

You’re iterating over this dict, so project is one of the dicts.

Within the template inside this loop at this location, project.name is then a reference to the dict element scores['name'].

How then do you think you might reference scores['scores'] in that same loop in the template?

20

I’m not sure.

I would have thought it would have worked with project.scores.scores being that scores exist as the key in the nested quesyset.

I’m thinking that maybe you are hinting towards another loop within the loop, but I did try this last night and couldn’t get close. So assumed that was wrong?

						{% for project in scores %}
							{% for scores in project.scores %}
							{{ scores }}
							{% endfor %} 
						{% endfor %}