Komplexe Queries

Bisher hatten wir es mit relativ einfachen Abfragen zu tun. Wir haben mit objects.all() alle Objekte einer Klasse selektiert und mit filter einfache Filteraufgaben durchgeführt.

Wir wissen nun, dass Filter-Chaining bzw. das mehrfache Verwenden von filter() auf ein Queryset in der Generierung des AND-Operators resultiert.

objects ist der sogenannte Manager und all() eine Methoden dessen.

der OR-Operator

Was ist aber zum Beispiel mit ODER? Mit dem Pipe-Symbol können wir auch OR-Abfragen erzeugen:

>>> # Alle Events aus dem Monat Januar des Jahres 2022, die Hobbit im
>>> # Namen haben. Mehrere Filter werden als SQL "AND" betrachtet.
>>> qs = Event.objects.filter(date__year=2022).filter(date__month=1)
>>> qs = qs.filter(name__icontains="Hobbit")
>>> qs
<QuerySet [<Event: Der Hobbit-Club>]>
>>>
>>> # einfache OR-Abfrage (Name beinhaltet großes H oder Sub-Titel beinhaltet kleines u)
>>> qs = Event.objects.all()
>>> qs = qs.filter(name__contains="H") | qs.filter(sub_title__contains="u")
>>> qs
<QuerySet [<Event: Der Hobbit-Club>, <Event: Harry Potter Leserunde>, ...]>
>>> str(qs.query)
'SELECT ... FROM "events_event" WHERE ... OR ...'

Allerdings ist diese Schreibweise nicht sonderlich elegant. Wir müssen immer qs.filter schreiben und die jeweils davon erzeugten Querysets mit den Pipe-Operator verbinden.

Komplexe Abfragen mit Q-Objekten

Falls wir aber komplexere Queries schreiben wollen, müssen wir das Q-Objekt nutzen. Wir können damit Queries quasi auslagern und OR - und AND- Operationen elegant verschachteln.

from django.db.models import Q

>>> # Filter: alle Events mit Harry im Namen ODER aus dem Jahr 2022
>>> # UND (die im Sub-Titel ein kleines "u" beinhalten oder aus dem Monat Februar sind)
>>> qs = Event.objects.filter(Q(name__contains="Harry") | Q(date__year=2022))
>>> qs = qs.filter(Q(sub_title__contains="u") | Q(date__month=2))
>>> qs
<QuerySet [<Event: Outdoor Boxen>]>

Ein weiteres Beispiel mit einer OR Verknüpfung mit dem | - Operator (Pipe).

Events die mit Sp anfangen UND (aus der Kategorie Sport oder aus der Kategorie Kochen sind):

qs = Event.objects.filter(
    Q(name__startswith='Sp'),
    (Q(category='Sport') | Q(category='Kochen'))
)

Ein Beispiel mit der Negation eines Q - Objekts mit dem ~ - Operator.

from django.db.models import Q

recent_years_end_events_excluding_books = (
    Event.objects.filter(
        Q(date__year__gt=2021),
        ~Q(category__name="Bücherwurm"),
        (Q(date__month=11) | Q(date__month=12)),
    )
)

Wir selektierten im Beispiel alle Events nach dem Jahr 2021, die NICHT aus der Kategorie Bücherwurm sind und entweder aus dem Monat November und Dezember stammen.

Um den Code noch sauberer zu halten, könnte man die Q-Objekte auch auslagern und später im Code verwenden. Das könnte mal bei wiederkehrenden Abfragen durchaus Sinn ergeben, sollte aber auch nicht übertrieben werden..

from django.db.models import Q

greater_2021 = Q(date__year__gt=2021)
not_books = ~Q(category__name="Bücherwurm")
month_11 = Q(date__month=11)
month_12 = Q(date__month=12)

recent_years_end_events_excluding_sport = (
    Event.objects.filter(
        greater_2021,
        not_books,
        (month_11 | month_12),
    )
)

Mehr zu Q-Objekten findet sich in der Django-Doku: https://docs.djangoproject.com/en/ref/topics/db/queries/#complex-lookups-with-q

Exkludieren (Sql NOT)

Um das Verhalten des NOT- Operators abzubilden, können wir Objekte exkludieren. Die Methode exlude() liefert uns dabei wieder ein Queryset.

# Alle Events, die nicht dem Kriterium entsprechen
qs = Event.objects.exclude(name="Kafka Freunde")
qs = qs.filter(year__2022)
<QuerySet [<Event: Harry Potter Leserunde>, <Event: Hobbit Club>, ...]>

F-Expressions

Eine F()-Expression repräsentiert den Wert eines Modelfelds, den transformierten Wert eines Modelfelds oder eine annotierte Spalte. Es ermöglicht, auf Modelfeldwerte zu verweisen und Datenbankoperationen mit ihnen durchzuführen, ohne sie tatsächlich aus der Datenbank in den Arbeitsspeicher ziehen zu müssen.

Nehmen wir an, wir möchten das min_group-Attribut jedes Event-Objektes um 1 hochzählen. Der erste, falsche Weg wäre, alle Event-Objekte mit all() zu laden, über sie zu iterieren und den Wert um 1 hochzählen.

Der bessere Weg ist über eine F-Expression, mit dem man direkt auf das Modelfeld verweisen kann.

1. Beispiel: Min_group Feld jedes Events um 1 erhöhen:

# falsch
events = Event.objects.all()
for event in events:
    event.min_group += 1
    event.save()

# richtig
from django.db.models import F
Event.objects.update(min_group=F('min_group') + 1)

Wir greifen mit F('min_group') auf den Feldwert zu und führen gleichzeitig ein update mit dem neuen Feldwert aus.

2. Beispiel: Name-Feld aller Einträge von Unterstrichen befreien und durch Leerzeichen ersetzen. Dazu benötigen wir die Funktionen Replace und Value aus den Model-Funktionen.

Value (aus der Django-Doku): Wenn man den Wert einer ganzen Zahl, eines booleschen Wertes oder einer Zeichenkette in einem Ausdruck darstellen muss, kann man diesen Wert in einen Value() einschließen. Ohne Value() würde das Statement in einem Fehler resultieren.

from django.db.models.functions import Replace
from django.db.models import Value
Event.objects.update(name=Replace('name', Value("_"), Value(" ")))

Aggregate

Generiere zusammenfassende Werte über ein gesamtes QuerySet. Aggregate geben meistens Werte wie Zahlen zurück. zb. Max oder Min eines Feldes.

1. Beispiel: Wir benötigen die Kategorie mit dem ältesten Event. Dazu importieren wir uns einige der Aggregatsfunktionen und wählen die Funktion Min:

>>> from django.db.models import Sum, Max, Min, Count, Avg
>>>
>>> oldest_event = Event.objects.aggregate(Min('date'))
{'date__min': datetime.datetime(2022, 5, 13, 11, 14, 36, tzinfo=<UTC>)}

# prüfen mit order_by
>>> Event.objects.order_by("date").first().date
datetime.datetime(2022, 5, 13, 11, 14, 36, tzinfo=<UTC>)

In der Ausgabe sehen wir, dass das Ergebnis von aggregate ein Dict mit dem Feldnamen als Key und dem Feldwert als Value. Wenn wir bei dem Aufruf von aggregate keinen Feldnamen angeben, ist er Name hier date__min.

Wir könnten aber auch einen Feldnamen definieren:

>>> oldest_event = Event.objects.aggregate(oldest=Min('date'))
>>> oldest_event["oldest"]
datetime.datetime(2022, 5, 13, 11, 14, 36, tzinfo=<UTC>)

2. Beispiel: Mit der Avg - Funktion lässt sich zum Beispiel auch die durchschnittliche Gruppengröße pro Kategorie herausfinden. Wir werden das Feld min_group allerdings erst später im Buch zum Event-Model hinzufügen, deshalb resultiert diese Abfrage aktuell noch in einem Fehler:

>>> categories = Category.objects.aggregate(avg_gz=Avg('events__min_group'))
>>> categories["avg_gz"]

Wir benutzen hier den doppelten Unterstrich, um Felder über Beziehungen hinweg zu referenzieren. Hier referenzieren wir auf das Feld min_group des Event-Models.

Annotationen

Mit Annotationen lassen sich zusätzliche Felder in die Ausgabemenge einfügen. Es handelt sich hier also um Aggregate für jedes Element im Set.

Beispiel 1: Ein neues Feld mit fixem Wert hinzufügen:

>> b = Event.objects.annotate(fixed_value=Value(3))
>> b.first().fixed_value
3

Beispiel 2: Alle aktiven Events zählen. Um ein GROUP BY zu erstellen, verwenden wir eine Kombination aus values und annotate:

number_of_active_events = (Event.objects
                            .filter(is_active=True)
                            .values("is_active")
                            .annotate(
                                total=Count("id")
                            )
                            )
number_of_active_events[0]["total"]
4

Die Methode values() erwartet optional als Argumente, die Feldnamen, die selektiert werden sollen, in unserem Fall also nur die Spalte is_active.

Mehr zur Methode values in der Django Doku: https://docs.djangoproject.com/en/stable/ref/models/querysets/#values

Beispiel 3: Die Anzahl der Events zählen, die einer Kategorie zugeordnet sind. Wir fügen jeder Kategorie ein neues Feld num_of_events hinzu, welches man später per Dot-Notation addressieren kann.

categories = (Category.objects
                .annotate(
                    num_of_events=Count("events"))
                )
categories.first().num_of_events
7

Das neue Feld kann auch für Filter-Operationen genutzt werden.

Beispiel 4: Im folgenden Beispiel zählen wir mit Count die Events, die das Zeichen „c“ im Namen haben und filtern dann die Kategorien, die mehr als zwei solcher Events besitzen. Count erwartet als Keyword-Argument optional filter, an das wir ein Q-Objekt übergeben.

categories_with_c = (Category.objects
                    .annotate(
                        c=Count('events',
                                filter=Q(events__name__contains='c')
                                ))
                    .filter(
                        c__gt=2
                    )
                    )

categories_with_c
<QuerySet [<Category: Freizeit>, <Category: Sport>]>

Beispiel 5: Events, die mindestens 40 Tage entfernt sind:

from django.db.models.functions import Now
from datetime import timedelta

    far_away = (Event.objects
                .annotate(
                    dg=F('date') - Now())
                .filter(
                    dg__gt=timedelta(days=40)
                )
                )

Wir annotieren jede Zeile mit der Differenz von Eventdatum und aktuellem Zeitpunkt und filtern auf Basis dieses Ergebnisses alle Events, bei denen diese Differenz größer als 40 Tage ist. Dazu nutzen wird die timedelta-Methode des datetime-Moduls.

Weiterführende Links

• https://books.agiliq.com/projects/django-orm-cookbook/en/latest

• https://docs.djangoproject.com/en/stable/models/querysets/