4

I have a Post and Profile model. I'm trying to find out the most common category in a list of user posts.

Here's my models:

class Post(models.Model):
    user = models.ForeignKey(User, blank=True, null=True)
    category = models.CharField(max_length=20, choices=CATEGORY_CHOICES, default='1')

class Profile(models.Model):
    user = models.ForeignKey(User, blank=True, null=True)

    def most_common_category(self):
        posts = Post.objects.filter(user=self.user)
        for post in posts:
            print(post.category) # 1, 1, 2, 3, 2, 2, 4, 1, 2, 2

How would I do this?

Zorgan
  • 8,227
  • 23
  • 106
  • 207
  • See http://stackoverflow.com/a/629691/3901060 for a way to do this using `annotate`. You would just change designation to category. – FamousJameous Apr 21 '17 at 22:25

2 Answers2

5

you can do it by using raw query. In raw query table must be the name that you given class Meta: or table name saved in database shema. 

most_common = Post.objects.raw(select 1 as id, category, count(category) from post group by category order by count(category) desc)

or you can use .values.

most_common = Post.objects.values("category").annotate(count=Count('category')).order_by("-count")
Thameem
  • 3,426
  • 4
  • 26
  • 32
  • NOTE: Everything within `Post.objects.raw(...)` is supposed to be a string. So it should be `Post.objects.raw('select 1 as id, category, count(category) from post group by category order by count(category) desc')` – Joshua Swain Nov 05 '21 at 16:30
0
from django.db.models import Count

most_common = Post.objects.annotate(mc=Count('category')).order_by('-mc')[0].mc

You can find more information in the docs

Danil
  • 4,781
  • 1
  • 35
  • 50
  • Just tried this and it's not ordering by the most common category. Edit: Now it works, thanks. – Zorgan Apr 21 '17 at 22:30
  • I've tried it again with more posts being category 2 instead of category 1, but it's still saying category 1 is the most common. This seems to only print the first category in the queryset. – Zorgan Apr 21 '17 at 22:38
  • you can check all categories in most common order `Post.objects.annotate(mc=Count('category')).order_by('-mc')` – Danil Apr 21 '17 at 22:41
  • It's not showing the categories in most common order, it's just going through them (`1, 1, 2, 1, 2, 2, 2, 1, 2, 2, 2,`) – Zorgan Apr 21 '17 at 22:49