How to use Group by clause without model's id in Django ORM?-CodePudding

This is my Model

class Model1(models.Model):
    category = models.CharField()
    name = models.CharField()

I want to get count of category. So I wrote ORM like below:

from django.db.models import Count

result = Model1.objects.values('category').annotate(Count('sns_type'))

but the result shown like below

<QuerySet [{'category': 'A', 'category__count': 1}, 
{'category': 'B', 'category__count': 1},...]>

I felt so weird, so I printed a query of this result. query shown like below

SELECT "example_table"."category", COUNT("example_table"."category") AS "category__count" 
FROM "example_table" 
GROUP BY "example_table"."category", "example_table"."id"

I don't know Why id is included in this query.

Query I want to use is like below:

SELECT "example_table"."category", COUNT("example_table"."category") AS "category__count" 
FROM "example_table" 
GROUP BY "example_table"."category"

CodePudding user response：

Use .order_by(…) ^[Django-doc]:

from django.db.models import Count

result = Model1.objects.values('category').annotate(
    Count('sns_type')
).order_by('category')

That being said, repeating the same category as a CharField is usually not a good idea. Usually one works with a ForeignKey to a Category model as part of database normalization ^[wiki]:

class Category(models.Model):
    name = models.CharField(max_length=32)


class Model1(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    name = models.CharField(max_length=32)

Then you query with:

from django.db.models import Count

Category.objects.annotate(model1_count=Count('model1'))