1
class SoundItem(models.Model):
    name = models.CharField(max_length=200)

class SoundCategory(models.Model):
    name = models.CharField(max_length=200)

class SoundItemReview(models.Model):
    sound = models.ForeignKey(SoundItem, on_delete=models.CASCADE, related_name='sound_reviews')
    categories = models.ManyToManyField(SoundCategory, related_name="sound_reviews")

For one instance of sound item (s), I can get its reviews with s.sound_reviews.all(), but how I count most frequent categories in its reviews (without having to manually iterate reviews and count categories)?

Eg for s there are 2 reviews:
review1: category1, category2
review2: category2, category3
then I want to get {"category2": 2, "category1": 1, "category3": 1}
Jedi Knight
  • 367
  • 2
  • 10

1 Answers1

1

You an order the SoundCategorys by the number of items:

from django.db.models import Count

SoundCategory.objects.alias(nitem=Count('sound_reviews')).order_by('-nitem')

or when related to a single SoundItem item:

from django.db.models import Count

SoundCategory.objects.filter(sound_reviews__sound=my_sound_item).alias(
    nitem=Count('sound_reviews')
).order_by('-nitem')
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • As I mentioned, for one instance of sound item, not overall statistics, plz – Jedi Knight Mar 19 '23 at 15:03
  • sent an improvement edit: annotate will allow to extract nitem later – Jedi Knight Mar 19 '23 at 15:19
  • @JediKnight: `.annotate(..)` will repeat the aggregate in the select and is therefore a more expensive query. If you do not need the field, it is better to use `.alias(..)` if you somehow need it, `.annotate(..)` is of course better than counting each iteration. – Willem Van Onsem Mar 19 '23 at 15:38