Query to categorize a model by the number of foreign key items

Question

I have the following models:

Family():
  family_id = models.CharField(db_index=True, max_length=100)

Individual():
  individual_id = models.TextField(db_index=True)
  family = models.ForeignKey(Family, on_delete=models.PROTECT)

I need to write a query to get the following result:

Family Count | Individual Count
1            |      3 
5            |      8 
10           |      19

So, I need to categorize families (not as their family_ids, but as their counts!) by the number of individuals they have. Is it possible? And if yes, how?

So far I am able to get how many individuals each family_id has:

overview_data = Family.objects.annotate(ind_count=Count('individual')).order_by('ind_count')

Now I need a way to sum them up by the number of individuals their groups have.

Please, see the answer. No field to group by, it should be grouped by the number of individuals present, summing up families. — Nikita Vlasenko, Aug 10 '20 at 15:38

Nikita Vlasenko · Answer 1 · 2020-08-10T15:42:05.573

Currently I was able to solve it not through SQL query, but using python. I would believe there can be purely SQL query but I was not able to come up with it. So, if you have any ideas, please, answer. Here is how I solved the issue:

from collections import Counter 

def _generate_overview_data(project):                                                                                      
    data = Family.objects.annotate(ind_count=Count('individual'))                                                                     
            .values_list('ind_count', flat=True)                                                     
    return sorted(Counter(data).items())

data looks like [1, 1, 1, 1, ..., 2, 2, ..., 3, 3, 3, ...] So, I count the number of occurrences of 1, 2, 3 and get the ordered list of type [[1, 89], [2, 10], ...] where 89 families have 1 individual, 10 families have 2 individuals, etc.; and thats' what I would prefer to get straight from SQL.

oh. give you a [post](https://stackoverflow.com/questions/48226944/calculate-max-of-sum-of-an-annotated-field-over-a-grouped-by-query-in-django-orm/48253635), hope you can got the trick, or based your list result, you can use itertools.groupby to solve it. — Blackdoor, Aug 13 '20 at 00:47

Query to categorize a model by the number of foreign key items

1 Answers1