nicht angemeldet
(Beste) Lösung für SQL Abfrage
Hello Guys!
Ich bräuchte eine kleine Entscheidungshilfe für eine SQL Abfrage.
Konkret geht es um die "pivotierte" Darstellung bei eine Abfrage über mehrere Tabellen. Es gibt schon andere Beiträge darüber aber noch keine Gegenüberstellung der einzelnen Methoden bzw. welche Methode ihr nehmen würdet (abhängig von der Situation).
PostgreSQL 8.3.9, PHP 5.3.2
Da diese Postgre Version über keine Funktion wie GROUP_CONCAT bei MySQL verfügt, müssen andere Lösungen her.
(Postgre Version 8.4.4 hat die Funktion crosstab(text sql), bringt das was?)
Es geht um folgende Struktur:
Tabelle User:
uid|name
--+-------
1 |hans
2 |max
3 |stefan
Tabelle Autos:
aid|hersteller
--+-----------
1 |Honda
2 |VW
3 |Audi
4 |BMW
5 |Fiat
Tabelle user_auto:
uid|aid
---+---
1 |1
1 |3
2 |5
3 |2
3 |4
3 |5
Ich möchte eine tabellarische Darstellung auf der Website erzeugen (foreach mit PHP) der Benutzer, eine Zeile pro Benutzer und deren Autos durch Beistriche getrennt.
Final result:
Name |Autos
------+----------
hans |Honda,Audi
max |Fiat
stefan|VW,BMW,Fiat
Was wäre die beste und/oder einfachste und/oder performanteste Methode das zu verwirklichen wenn pro Seite 100 Namen angezeigt werden mit durchschnittlich je 3 Autos.
Meine Vorschläge:
1.)
Eine normale Abfrage mit JOINs wodurch zwangsweise die Namen der Benutzer doppelt im Ergebnis vorkommen.
SELECT User.name, Autos.hersteller FROM User JOIN Autos JOIN user_auto ON user_auto.uid = User.uid AND user_auto.aid = Autos.aid
name|hersteller
----+----------
hans|Honda
hans|Audi
...
Bei weiterer PHP Verarbeitung müsste dann bei der foreach Schleife darauf geachtet werden, dass die Namen nicht doppelt ausgegeben werden aber die Hersteller richtig mit Beistrichen getrennt den Namen zugeordnet werden.
SQL technisch wohl am einfachsten zu realisieren, das Array kann aber bei entsprechend vielen Datensätzen ordentlich anwachsen.
2.)
Nur die User abfragen und bei der Ausgabe (PHP foreach) bei jedem Durchlauf eine SQL Abfrage auf den Autohersteller machen mit der jeweiligen uid.
SELECT Autos.hersteller FROM Autos JOIN user_auto ON user_auto.aid = Autos.aid AND user_auto.uid = $uid
Das Ergebnis dann entsprechend anpassen um die Hersteller wieder mit einem Beistrich zu trennen.
Meiner Meinung nach umständlich und viel zu viele SQL Abfragen (>100).
3.)
Mittels einer eigenen Aggregate Function die Hersteller schon bei der Abfrage entsprechend zusammenbauen (Diese Funktion wird schon bekannt sein).
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
SELECT User.name,array_to_string(array_accum(Autos.hersteller), ',') AS hersteller FROM User JOIN Autos JOIN user_auto ON user_auto.uid = User.uid AND user_auto.aid = Autos.aid GROUP BY User.name
Vorteil von dieser Methode ist, dass ich mit bei der PHP Schleife keine Gedanken mehr machen muss wegen der Vermeidung doppelter Namen, da mein Ergebnis der Abfrage im Prinzip schon so aussieht wie ich meine Tabelle im Endeffekt darstellen möchte.
Die Frage ist, wie performant is diese Methode bei entsprechend vielen Datensätzen?
Gibt es noch andere Lösungen die noch einfacher / besser / performanter sind? Welche Methode würdet ihr nehmen? Was sind eure Meinungen dazu?
LG Emanuel
-
Hallo,
Gibt es noch andere Lösungen die noch einfacher / besser / performanter sind?
Performanz:
Was haben *Deine* Messwerte mit *Deinen* Daten ergeben?
Befürchtest Du einen Flaschenhals?- ist einfach und elegant.
- wird vermutlich elend lahm sein
Wie steht *Deiner* Meinung nach 1) zu 3)
Freundliche Grüße
Vinzenz
-
Hallo,
Was haben *Deine* Messwerte mit *Deinen* Daten ergeben?
Befürchtest Du einen Flaschenhals?Nun ja, ich habe eben noch keine Daten aber stehe eben vor der Entscheidung welche der Abfragen ich verwenden sollte für die angesprochene Aufgabe ohne in Zukunft viel daran ändern zu müssen, weil die performance zu schlecht ist.
Ich habe auf Erfahrungswerte der Community gehofft :)- ist einfach und elegant.
Auch aus eigener Erfahrung heraus oder nur "auf den ersten Blick"?
Wie steht *Deiner* Meinung nach 1) zu 3)
Naja, ich denke, dass 1) die einfachste und schnellste SQL Abfrage darstellt.
Die Frage ist, was mehr Leistung benötigt, das PHP Skript oder die SQL Abfrage.
Es sollte nach 1) kein Problem sein die gewollte Darstellung mittels PHP zu erreichen, das Ergebnisarray aus der SQL Abfrage ist aber 3 - 4 x so groß wie bei 3)."Schöner" ist 3) aber auch (merkbar?) langsamer... oder nicht?
LG Emanuel