CODEWITHSUNDEEP
pythondjangodjango-models
Dipesh Bajgain
Dipesh Bajgain

Reputation: 839

How to count total occurrence of distinct field of same model in django?

I am trying to get the total count of occurrence of a field in same model in django. Is it possible to do it or not?

for example I have a model as below:

class Blog(DateTimeModel):
    title = models.CharField(max_length=255)
    description = models.TextField()
    category = models.CharField(max_length=255, null=True, blank=True)

and a table of data as below:

 id | title                               | description          | category
----+-------------------------------------+----------------------+----------
  1 | blog title first                    | description 1        | social
  2 | blog title second                   | description 2        | social
  3 | blog title third                    | description 3        | community
  4 | blog title fourth                   | description 4        | community
  5 | blog title fifth                    | description 5        | people

I want a result to be as:

<QuerySet [{'category': 'social', 'blog_count': '2'}, {'category': 'community', 'blog_count': '2'}, {'category': 'people', 'blog_count': '1'}]>

I have tried doing Blog.objects.values('category').order_by('category').distinct('category').count() and got result as 3 which is true because it had count the total distinct values related to category.

Django annotate count with a distinct field this post provide a solution where we can count fields based on two models. But i want the results from the same model.

Upvotes: 2

Views: 1741

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476614

You can obtain such queryset with:

from django.db.models import Count

Blog.objects.values('category').annotate(
    blog_count=Count('pk')
).order_by('category')

That being said, the modeling might not be optimal, since it creates a lot of data duplication. Imagine if you later want to rename a category, then this would result in a huge amount of work, since these categories can be spread over a lot of tables.

It makes more sense to then implement a Category model, like:

class Category(models.Model):
    name = models.CharField(max_length=255, unique=True)

class Blog(DateTimeModel):
    title = models.CharField(max_length=255)
    description = models.TextField()
    category = models.ForeignKey(Category, null=True)

Then you can annotate the Category objects, like:

from django.db.models import Count

Category.objects.annotate(
    blog_count=Count('blog')
)

Upvotes: 4

Related Questions