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 [email protected].
To post to this group, send email to [email protected].
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/a19b5303-9625-45d6-a007-9f06c775869f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
