Get value of related field using MAX value with annotate.

Having a bit of a struggle with what seems like it should be a simple issue...

Essentially I have some sites where counts of objects occurred over several years:

Example:
site_id = Site1: (Year:2012,Count:133), (Year:2011, Count:150), (Year:2010, Count :110)

site_id = Site2: (Year:2010, Count:300), (Year:2010, Count 333)

Data are temporally patchy (not regular - some sites were counted some years.. others not...).. also, sometimes those places were counted a couple of times a year

What I want to do is get the LATEST count for each site, and IF there was more than one count, I want to get the HIGHEST count.. Then I want to display that in HTML.

here is my MODELS.PY

class Counts(models.Model):      count_id = models.AutoField(primary_key=True)      site = models.ForeignKey('Site', blank=True, null=True)      year = models.IntegerField(blank=True, null=True)      count = models.FloatField(blank=True, null=True)        class Meta:          db_table = 'counts'      class Site(models.Model):      site_id = models.TextField(primary_key=True)      site_code = models.TextField(blank=True, null=True)      site_name = models.TextField(blank=True, null=True)        class Meta:          db_table = 'site'

This is the Query I am trying to use in VIEWS.PY

p = ['Site1','Site2']  ## Just for reference for the example... values come from a POST or a GET    A = Site.objects.filter(site_id__in = p).annotate(latest=Max('counts__year'))    context = RequestContext(request, {'dat':A})  template = loader.get_template('styles/searchResults.html')          return HttpResponse(template.render(context))

The above gives me only the latest years:

[{'site_id': u'Site1','latest': 2012}, {'site_id': u'Site2','latest': 2010}]

What I want is:

[{'site_id': u'Site1','latest': 2012,'count':133}, {'site_id': u'Site2','latest': 2010,'count':333}]

But - I want it as a QuerySet (not a ValuesQuerySet) because I want to reference it in my HTML template like this:

<table>  {% for x in dat %}      <tr><td>{{x.count|floatformat}}</td><td>{{x.year}}</tr>  {%endfor%}  </table>

I have tried the following (after creating A from above): 

B = Counts.objects.filter(year__in = A.values('latest'),site__site_id__in = p).annotate(site_code=Max('site__site_id'))

But this results in essentially: 

[{'site_id': u'Site1','latest': 2012,'count':133},{'site_id': u'Site1','latest': 2010,'count':110}, {'site_id': u'Site2','latest': 2010,'count':333},{'site_id': u'Site2','latest': 2010,'count':300}]

In other words, it is pulling out all values where YEAR = 2010 OR 2012 for both sites.

Again, what I'm looking for is the HIGHEST count for the LATEST year. Max(count), Max(year) - I'm sure that plays in somehow...

Thanks!

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a76325f2-775e-4e37-b0aa-6399206277d4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.