Searching and Count records matching criteria in Django

Question

I'd like to have a sorted list of certain database fields in my app e.g say year (where I'll have all records having a certain year tallied and the total displayed as in: 2009 (10), 2008(3) etc) then clicking on a certain year will display these records.

How should I do this?

EDIT 1

class Year(models.Model):
  year = models.PositiveSmallIntegerField()

  def __unicode__(self):
     return unicode(self.year)

class CommonVehicle(models.Model):
  year = models.ForeignKey(Year)
  series = models.ForeignKey(Series)

  def __unicode__(self):
    name = ''+str(self.year)+" "+str(self.series)
    return name 

class Vehicle(models.Model):
   stock_number = models.CharField(max_length=6, blank=False)
   vin = models.CharField(max_length=17, blank=False)
   common_vehicle = models.ForeignKey(CommonVehicle)

   def __unicode__(self):
     return self.stock_number

Answer 1

Not sure if this helps, but you can count the number of objects in a filtered query simply by doing:

this_year = Widget.object.filter(you_date_field__year=2009).count()

Answer 2

The query for the summary list should look something like this (Django 1.1+ only):

from django.db.models import Count
Widget.objects.order_by('year').values('year').annotate(count=Count('id'))

This will return a list of dictionaries, something like the following:

[{'count': 3, 'year': 2006}, {'count': 1, 'year': 2007}, {'count': 2, 'year': 2009}]

You can easily iterate through this in your template to generate your list of links:

{% for item in year_counts %}
  <li><a href="{% url year_view year=item.year %}">{{ item.year }} ({{ item.count }})</a></li>
{% endfor %}

Where "year_view" is a view you create up that accepts a "year" parameter and displays a filtered list of objects like:

Widget.objects.filter(year=year)