Home > Software design >  Django - Counting ManyToMany Relationships
Django - Counting ManyToMany Relationships

Time:01-20

In my model, I have many Things that can have many Labels, and this relationship is made by user-submitted Descriptions via form. I cannot figure out how to count how much of each Label each Thing has.

In models.py, I have:

class Label(models.Model):
    name = models.CharField(max_length=100)

class Thing(models.Model):
    name = models.CharField(max_length=100)

class Description(models.Model):
    thingname = models.ForeignKey(Thing, on_delete=models.CASCADE)
    labels = models.ManyToManyField(Label,blank=True)

If we say our current Thing is a cat, and ten people have submitted a Description for the cat, how can we make our template output an aggregate count of each related Label for the Thing?

For example: Cat

  • 10 fluffy
  • 6 fuzzy
  • 4 cute
  • 2 dangerous
  • 1 loud

I've tried a few things with filters and annotations like

counts = Label.objects.filter(description_form = pk).annotate(num_notes=Count('name'))

but I think there's something obvious I'm missing either in my views.py or in my template.

CodePudding user response:

You can use this to retrive this information:

Description.objects.prefetch_related("labels").values("labels__name", "thing_name__name").annotate(num_notes=models.Count("labels__name"))

this will be equal to:

SELECT "core_label"."name",
   "core_thing"."name",
   Count("core_label"."name") AS "num_notes"
FROM   "core_description"
   LEFT OUTER JOIN "core_description_labels"
                ON ( "core_description"."id" =
                     "core_description_labels"."description_id" )
   LEFT OUTER JOIN "core_label"
                ON ( "core_description_labels"."label_id" =
                     "core_label"."id" )
   INNER JOIN "core_thing"
           ON ( "core_description"."thing_name_id" = "core_thing"."id" )
GROUP  BY "core_label"."name",
      "core_thing"."name" 
  • Related