Tallying records using annotate() not working as should.

Question

I have two classes: Vehicle and Issues....a Vehicle object can have several issues recorded in the Issues class. What I want to do is to have a list of all issues, with each vehicle appearing only once and the total number of issues shown, plus other details....clicking on the record will then take the user to another page with all those issues for a selected vehicle shown in detail now.

I tried this out using annotate, but I could only access the count and vehicle foreign key, but none of the other fields in the Vehicle class.

class Issues(models.Model):
   vehicle = models.ForeignKey(Vehicle)
   description = models.CharField('Issue Description', max_length=30,)
   type = models.CharField(max_length=10, default='Other')
   status = models.CharField(max_length=12, default='Pending')
   priority = models.IntegerField(default='8', editable=False)
   date_time_added = models.DateTimeField(default=datetime.today, editable=False)
   last_updated = models.DateTimeField(default=datetime.today, editable=False)
   def __unicode__(self):    
     return self.description

The code I was using to annotate is:

issues = Issues.objects.all().values('vehicle').annotate(count=Count('id'))

What could be the problem?

Answer 1

You've specifically requested to only have the vehicle in the returned queryset, via the call to values, so why are you surprised when that is indeed the result?

Answer 2

I believe what you are trying to do, should be queried the other way around, like this:

vehicles = Vehicle.objects.all().annotate(count_issues=Count('issues__pk'))

Now you'll have a queryset of Vehicle objects, thus you have all vehicle fields. And you'll have an extra field 'count_issues' for each vehicle.

Edit: You can simply use a filter on the extra column from annotate:

vehicles = Vehicle.objects.all().annotate(count_issues=Count('issues__pk')).filter(count_issues__gt=0)