I have a table like this

And I need to find in how many categories each subcategory appears. So my expected output based on above table would be:

So I can get that by running following SQL query:

SELECT subcategory, count(*) total FROM (
    SELECT DISTINCT subcategory, category FROM table_1
) as temp_table GROUP BY subcategory ORDER BY total DESC

I spent a lot of time trying to get the same result with Django ORM but wasn't able to get it done. I expected this code to work:

subquery = Table1.objects.values('subcategory', 'category').distinct()
results = subquery.annotate(total=Count('*')).values('subcategory', 'total').order_by('-total')

But it works exactly the same as without 'distinct()' in subquery, so it counts all categories for every subcategory.

I also tried to find similar case in other questions, but those with subqueries usually relate to JOINing tables and using OuterRef, here it is more like getting results based on temporary table that is created by subquery.

Does anyone know how can I achieve that (or if it's even possible)?

CodePudding user response：

this should do the job

dictionary = {}
queryset = YOUR_queryset_with_table_HERE.objects.all()

for i in queryset:
    subcategory = i.subcategory
    
    #check if subcategory already in dictionary
    #if not create it with 1, else  =1
    
    if dictionary["subcategory"]:
        dictionary["subcategory"]  = 1
    else:
        dictionary["subcategory"] = 1

print(dictionary)

CodePudding user response：

Say you have two models in a many-to-many relationship similar to this:

class Category(models.Model):
    category_name = models.CharField(max_length=30)

class SubCategory(models.Model):
    subcategory_name = models.CharField(max_length=30)
    category = models.ManyToManyField(Category)

Then this should give you the total number of categories in which each subcategory appears:

results = SubCategory.objects.all().annotate(total=Count('category')).values('subcategory_name', 'total')