Jeder Round-Trip zur Datenbank kostet Zeit und Ressourcen. Oftmals hat man aber das Problem, dass man zu einem Datum noch eine unspezifizierte Anzahl von Relationen braucht. Meistens holt man die dann in einem Loop in der Applikation, denn üblicherweise bekommt man in den Ergebnisfeldern eines SQL-Queries nur flache Daten zurück. Mit PostgreSQL und JSON in der Datenbank geht das in manchen Fällen aber eleganter – und man kann ein gutes Dutzend Queries auf genau eine schnelle Abfrage reduzieren.

Ziel in diesem Beispiel ist es, für eine Suche nach einem Produktnamen jeweils pro eindeutigem Namen einen kleinen Block auszugeben, der diesen Produktnamen enthält, dazu das Bild zur bevorzugten zugehörigen Entität (z.B. bei Assassin’s Creed die PC-Version), den Link zur Produktseite dieser Entität, die Plattform (hier PC), ebenfalls verlinkt und dann in einer von der Redaktion vorgegebenen Reihenfolge von anderen Plattformen jeweils den Plattformnamen mit Link auf die zugehörige Produktseite. Wir haben also in dem benötigten Datensatz zu einem Namen genau einen Bild-Pfad, genau einen Namen und jeweils ein bis n Plattform-Bezeichner und URLs.

Wir haben eine Tabelle public.entity_autosuggest, die im Prinzip eine gruppierte materialized View unserer Produktdatenbank darstellt, d.h. für jeden eindeutigen Produktnamen gibt es ein Tupel mit u.a. einem Array der zugehörigen Entity-IDs und Entity-Type-IDs; die beiden Arrays sind automatisch in korrelierender Reihenfolge angelegt, d.h. die Position 1 im Entity-Type-ID-Array enthält dann die Type-ID für die gleiche Position im Entity-ID-Array. Diese Reihenfolge ist allerdings zufällig, es gibt keine Vor-Sortierung nach Plattform-Typen.

Die Tabelle eignet sich u.a. für Trigramm-Suchen, es gibt aber auch einen Sphinx-Index, der natürlich nur die id der matchenden entity_autosuggest-Tupel zurückliefert; passend dazu gibt es eine pl/pgsql-Funktion, mit der man zu einer solchen id alle Namen, alle Entity-IDs, alle Entity-Typ-Bezeichner und auch alle SEO-URLs für die Entitäten für eine gegebene Site bekommen kann, fertig gefiltert und sortiert nach einer Liste von Entity-Type-IDs. Der Kopf dieser Funktion ist dabei wie folgt:

Beispiel für die Verwendung:

D.h. das Ding ist ein Record-Returning-Function, es liefert immer einen komplexen Datensatz mit den Feldern entity_name, arrentity_ids, arrentity_platforms und arrentity_urls zurück und wird verwendet wie eine Tabelle.

Jetzt bekomme ich von Sphinx eine Liste von entity_autosuggest.id-Werten – und ich möchte für die komplette Liste in einem einzigen Trip alles haben, was ich für die Ausgabe im Frontend benötige, d.h. für alle Treffer jeweils Name, Entity-IDs, URLs, Plattformen und ein Bild der als erstes angezeigten Entität. Das geht, aber die Syntax für den Zugriff auf die Felder der komplexen Rückgabewerte ist etwas gewöhnungsbedürftig – da braucht’s eine Klammer, damit PostgreSQL realisiert, dass es sich hier um einen Feldpfad handelt und nicht um ein Schema:

Laufzeit der Abfrage auf Live-Daten (>64.000 Produkte) einen winzigen Tick über 30 Millisekunden. Die Applikation muss dann nur noch server_info deserialisieren und bekommt mundgerecht aufbereitete Arrays für die Ausgabe.

Kommentar verfassen