How to get latest of many groups at once

Question

I have a model like this:

class Foo(models.Model):
    date = models.DateTimeField()
    language = models.TextField()
    # other stuff

And I want to group the Foos by language, then get the latest one in each group. I haven't been able to figure out how to use django's QuerySet API to do this (honestly, I don't know how to do it in SQL either). So for example:

pk | date   |    language
---+--------+------------------
1  | 1:00   |    python
2  | 1:30   |    python/django
3  | 1:45   |    haskell
4  | 2:15   |    python
5  | 2:45   |    haskell

I want to get something resembling this result:

{ 'python': 4, 'python/django': 2, 'haskell': 5 }

Where perhaps instead of numbers those are complete Foo objects.

Answer 1

You can use a raw query to solve your problems:

query = Foo.objects.raw('SELECT id, language, MAX(date) FROM sqltest_foo GROUP BY language;')
for foo in query:
    print foo.id, foo.language

Obs: I'm using SQLite syntax, but other SQL languages should be similar.