I am trying to write a queryset operation that transforms the first table into the second table as efficiently as possible This is the criteria: For each name, how many unique schools are affiliated with it? Also, the exact names and schools are unknown beforehand.
| Name | School |
|---|---|
| John | USC |
| John | USC |
| John | UCLA |
| Adam | UCSD |
| Adam | USC |
| Name | num_unique_schools |
|---|---|
| John | 2 |
| Adam | 2 |
1- Using Values method:
The students will be grouped by name, so you will only get an annotated result for each unique student name.
2- Using Count Method with distinct argument:
This is counting a "school" based a grouped student name , also remove duplicated schools.
code:
from django.db import models
results = StudentSchool.objects.values('name').annotate(
num_unique_schools = models.Count('school', distinct=True)
).order_by("name")
Test Case
