Hi everyone,
I'm brand-new to Django so this may be a noob question; however, I've
been stuck on this one query for a couple of days now and can't seem
to reach a solution. I'm working with a model called Update, which
represents an update to a user's score in a match. I am trying to
return the most recent score for each user in a given match. Here's my
Update class:
class Update(models.Model):
updater = models.ForeignKey(User, related_name='updaterUser')
updatee = models.ForeignKey(User, related_name='updateeUser')
match = models.ForeignKey(Match)
createdOn = models.DateTimeField(auto_now_add=True)
lastUpdated = models.DateTimeField(auto_now=True,auto_now_add=True)
isLatest = models.BooleanField()
score = models.IntegerField()
def __unicode__(self):
return 'Update to %s: %s updated %s at %s' % (self.match.name,
self.updater, self.updatee, self.lastUpdated)
and here is the view where I am trying to fetch the correct data:
def matchDetail(request, match_id):
m = get_object_or_404(Match, id=match_id)
matchUpdates = Update.objects.filter(match=m)
u = matchUpdates.order_by('-lastUpdated')
s =
matchUpdates.filter(pk__in=(matchUpdates.values('updatee').annotate(Max('pk'))))
return render_to_response('match.html', {'match': m, 'updates': u,
'scores': s})
Basically what I need to do is return one row for each updatee where
lastUpdated is highest for that particular updatee. I tried a few
different things for s. First I tried annotating the updatees with the
highest lastUpdated datetime:
s = matchUpdates.values('updatee').annotate(Max('lastUpdated'))
This returned the correct results, but was useless, because I need to
return the whole row, not just the values for updatee. Adding other
columns to values() resulted in incorrect results, because annotate
looks at all columns, and I need it to look at updatee only. Then I
tried annotating within a subquery, which I thought would work for
sure:
s =
matchUpdates.filter(pk__in=(matchUpdates.values('updatee').annotate(Max('pk'))))
but it threw me an error: "Caught DatabaseError while rendering: only
a single result allowed for a SELECT that is part of an expression." I
can't really find documentation on this error, but as far as I can
tell, it's an issue with sqlite and not with Django.
If it helps at all, this could be accomplished with the following SQL:
SELECT updatee, score, id
FROM Update
WHERE lastUpdated = (
SELECT max(lastUpdated)
FROM Update as u
WHERE u.updatee = Update.updatee)
I am really stumped on how to accomplish this in Django, and would
prefer not to use raw SQL if I can help it. Can anyone point me in the
right direction?
Thanks very much for your help.
-A
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
