Get value of related field using MAX value with annotate.

[Grant Humphries]

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!