.. _complex_queries: 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: .. code-block:: python >>> # 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 ]> >>> >>> # 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 , , ...]> >>> 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. .. code-block:: python 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 ]> 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): .. code-block:: python 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. .. code-block:: python 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.. .. code-block:: python 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: ``_ Exkludieren (Sql NOT) ================================== Um das Verhalten des ``NOT``- Operators abzubilden, können wir Objekte exkludieren. Die Methode ``exlude()`` liefert uns dabei wieder ein Queryset. .. code-block:: python :linenos: # Alle Events, die nicht dem Kriterium entsprechen qs = Event.objects.exclude(name="Kafka Freunde") qs = qs.filter(year__2022) , , ...]> 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: .. code-block:: python # 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. .. code-block:: python 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``: .. code-block:: python >>> 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=)} # prüfen mit order_by >>> Event.objects.order_by("date").first().date datetime.datetime(2022, 5, 13, 11, 14, 36, tzinfo=) 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: .. code-block:: python >>> oldest_event = Event.objects.aggregate(oldest=Min('date')) >>> oldest_event["oldest"] datetime.datetime(2022, 5, 13, 11, 14, 36, tzinfo=) **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: .. code-block:: python >>> 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: .. code-block:: python >> 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``: .. code-block:: python 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:** ``_ **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. .. code-block:: python 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. .. code-block:: python categories_with_c = (Category.objects .annotate( c=Count('events', filter=Q(events__name__contains='c') )) .filter( c__gt=2 ) ) categories_with_c , ]> **Beispiel 5:** Events, die mindestens 40 Tage entfernt sind: .. code-block:: python 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 ....................... * ``_ * ``_