Django - Unexpected result from .values and .annotate

Question

I'm trying to count the number of occurrences of the different product names in a table. I would like the result to be something like this:

Laptop 10
Mice 15

And so on, yet instead I get:

2 10
1 15

Any ideas what could be wrong?

This is my models.py:

class ProductNoSerial(models.Model):
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category)

    def __str__(self):
        return self.name

class ProductNoSerialInstance(models.Model):
    STATUS_CHOICES = (
      ('in_inventory', 'In Stock'),
      ('given_out', 'Given Out'),
      ('repair', 'Repair')
    )
    name = models.ForeignKey(ProductNoSerial)
    owner = models.ForeignKey(Employee, blank=True, null=True)
    it_dep = models.ForeignKey(ItDep)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='draft')

    def __unicode__(self):
        return self.name

This is my view.py:

def InventoryList(request):
  count_query = ProductNoSerialInstance.objects.values('name').annotate(count=models.Count('name'))
  context = { 'inventory_list': count_query }
  return render(request, 'inventory_list.html', context)

And finally my inventory_list.html:

<p><b>List of inventory<b></p>
<table>
{% for item in inventory_list %}
<tr>
<td> {{ item.name }} </td>
<td> {{ item.count }} </td>
</tr>
{% endfor %}
</table>

I've tried these suggestions, but without any luck. Any ideas what might be wrong? I figured the problem could be caused by the fact that name is a ForeignKey?

@albar , If you are thinking it's ask a right answer. Write it on answer. — Rahul K P, May 11 '17 at 13:00
@albar Hi albar, thanks for your suggestion. The 1st column has simply disappeared now, yielding `10 15` — Jakob Handberg Lystbæk, May 11 '17 at 13:03
You're querying the wrong thing. You should be running the annotation on ProductNoSerial, and count the number of related ProductNoSerialInstances. — Daniel Roseman, May 11 '17 at 13:06

score 2 · Accepted Answer · answered May 11 '17 at 13:05

2

There is a subtlety here that's not very obvious at first glance because the ProductNoSerialInstance model has a field named name which is actually a foreign key. Couple that with the fact that you are getting values and this is the result. values return dictionaries rather than object instances so {{ item.name.name }} isn't unfortunately going to work. you need to make a slight modification to your query

ProductNoSerialInstance.objects.values('name__name').annotate(count=models.Count('name'))

answered May 11 '17 at 13:05

e4c5

52,766
11
101
134

Hi e4c6, this works as intended! I of course also had to change ` {{ item.name}} ` to ` {{ item.name__name }} ` in the `inventory_list.html`. Thanks you so much! – Jakob Handberg Lystbæk May 11 '17 at 13:13
Glad to have been of help. – e4c5 May 11 '17 at 13:13

Django - Unexpected result from .values and .annotate

1 Answers1