Group By Funktionen in Querysets

In meinem aktuellen Projekt will ich die Anzahl neuer Einträge in einem Model pro Tag in einer Grafik darstellen, ich habe also ein Problem wie:

Gib mir eine Liste aller Tage an denen Beiträge hinzugefügt wurden und wie viele Beiträge waren es an diesem Tag.

Ein SQL-Query dafür ist einfach:

SELECT count(id) as counter, created FROM entry_table GROUP BY created

Nun schauen wir mal, wie sich das mit Django 1.1 Bordmitteln lösen lässt. Wir erstellen ein Django-Model:

from django.db import models

class Entry(models.Model):
    created = models.DateField()
    title = models.CharField(max_length=120)

Und füttern es gleich mit ein paar Testdaten:

>>> Entry.objects.create(created='2009-03-25', title='Irgendein Titel')
>>> Entry.objects.create(created='2009-03-24', title='Irgendein Titel')
>>> Entry.objects.create(created='2009-03-24', title='Irgendein Titel')

Während man in Django 1.0 noch rohes SQL walten lassen musste, wurden Django 1.1 (aktuell noch django-trunk) einige Aggreagations-Funktionen hinzugefügt, um übliche Aufgaben wie Count, Average, Max und Min etc. direkt im Queryset durchführen zu können. Für unseren Fall brauchen wir die Count-Funktion.

>>> from django.db.models import Count
>>> Entry.objects.values('created').annotate(counter=Count('id'))
[{'counter': 2, 'created': datetime.date(2009, 3, 24)},
 {'counter': 1, 'created': datetime.date(2009, 3, 25)}]

values('created') triggert hierbei das "GROUP BY created" und Count('id') ist das Pendant zur SQL-Funktion COUNT(id). annotate fügt diese Daten dem SELECT hinzu.

Super einfach, oder? :)

Umgang mit DateTime Feldern

Nun ist es aber in der Regel so, dass man einem Artikel kein DateField sondern ein DateTimeField zuweist, also die Zeit mitspeichert:

class Entry(models.Model):
    created = models.DateTimeField()
    title = models.CharField(max_length=120)

Füttern wir das Model wieder mit ein paar Testdaten:

>>> Entry.objects.create(created='2009-03-25 12:30:00', title='Irgendein Titel')
>>> Entry.objects.create(created='2009-03-24 10:25:00', title='Irgendein Titel')
>>> Entry.objects.create(created='2009-03-24 8:56:00', title='Irgendein Titel')

Und führen unseren obigen Query aus:

>>> Entry.objects.values('created').annotate(counter=Count('id'))
[{'counter': 1, 'created': datetime.datetime(2009, 3, 24, 8, 56)},
 {'counter': 1, 'created': datetime.datetime(2009, 3, 24, 10, 25)},
 {'counter': 1, 'created': datetime.datetime(2009, 3, 25, 12, 30)}]

Man sieht, das Ergebnis ist unbrauchbar. Logisch, denn es wird nach Datum und Zeit gruppiert. Wir brauchen also nur das Datum aus dem DateTime-Objekt. In SQL gibt es dafür die Funktion DATE(datetime-Feld) die das Datum extrahiert:

SELECT COUNT(id) as counter, DATE(created) as day FROM entry_table GROUP BY day

Dummerweise liefert Django keinen Filter mit, um eine solche Aufgabe zu lösen. Es gibt zwar die dates Methode, um Zeitperioden aus einem Queryset zu extrahieren, diese liefert aber nur die DateTime-Objekte zurück, kein komplettes Queryset, dass für annotate notwendig ist.

Es muss also doch (etwas) rohes SQL her. Dafür ist die extra Methode gedacht. Mit ihr kann man rohes SQL in den Query einsetzen. Folgendes Beispiel resultiert gleich:

Queryset: Entry.objects.extra(select={'day': 'DATE(`created`)'})
SQL:      SELECT *, DATE(`created`) AS day FROM entry_table

Zusammengefügt schaut unser Queryset jetzt so aus:

>>> entries = Entry.objects.extra(select={'day': 'DATE(`created`)'})
    .values('day').annotate(counter=Count('id'))
>>> entries
[{'counter': 2, 'day': u'2009-03-24'}, 
 {'counter': 1, 'day': u'2009-03-25'}]

Die Tage sind hierbei aber ein unicode-String, ein DateTime-Objekt wäre schöner. Mappen wir den Queryset noch einmal:

>>> entries = Entry.objects.extra(select={'day': 'DATE(`created`)'})
    .values('day').annotate(counter=Count('id'))
>>> entries = [{'counter': i['counter'], 'day': datetime.strptime(i['day'], '%Y-%m-%d')} for i in entries]
>>> entries
[{'counter': 2, 'day': datetime.datetime(2009, 3, 24, 0, 0)},
 {'counter': 1, 'day': datetime.datetime(2009, 3, 25, 0, 0)}]

Da sind sie, die Tage an denen Beiträge hinzugefügt wurden inkl. deren Anzahl. :-)

War diesmal vielleicht (noch) verworrener als sonst, wer Fragen dazu hat kann mir wie immer mailen oder in die Kommentare schreiben. :-)