CODEWITHSUNDEEP
djangoannotations
Stephen
Stephen

Reputation: 6319

Tallying records using annotate() not working as should

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?

Upvotes: 0

Views: 123

Answers (2)

Botond Béres
Botond Béres

Reputation: 16673

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)

Upvotes: 1

Daniel Roseman
Daniel Roseman

Reputation: 599530

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?

Upvotes: 0

Related Questions