MySql: geJOINTe Tabellen mit Wiederholungsgruppe(n) möglichst elegant auslesen

[JensB]

Hallo mal wieder,

ich programmiere zurzeit in PHP ein Datenbanksystem, mit dem verschiedene Elektronenröhren verwaltet werden sollen.

Nun hat jede Röhre einen eindeutigen Namen und eine eindeutige ID in der Mastertabelle "mastertable", klar so weit. Nun sollen aber auch Bilder und Links zu den Röhren hinzugefügt werden. Sprich: Wiederholungsgruppen.

Also habe ich diese ausgelagert in "roehren_bilder" und "roehren_links"; diese Tabellen bestehen aus jeweils ID, master_ID (die verweist auf die ID der Röhre im mastertable) und einem text link bzw. bild. Das Auslesen mache ich momentan so (wohlgemerkt, mit einer Wiederholungsgruppe, nur den Bildern):

SELECT  * 
FROM mastertable
INNER JOIN roehren_bilder ON mastertable.id = roehren_bilder.master_id

Oder so mit den Links:

SELECT  * 
FROM mastertable
INNER JOIN roehren_bilder ON mastertable.id = roehren_bilder.master_id

Dann erhalte ich ja den Namen der Röhre genau so oft als Zeile, wie es Bilder bzw. Links dazu gibt. Wie kann ich elegant alle Bilder und Links auslesen?

Vielleicht lieber LEFT JOIN nehmen und bei leeren mastertable-Daten die Wiederholungsgruppen zur letzten Röhre hinzufügen?

__________

Eine Alternative ist es, nach wie vor

SELECT * FROM mastertable

zu fragen, und dann pro Röhren-Ergebnis in den Bild- und Linktabellen nach Einträgen zu suchen. Allerdings kommt mir das eher krude vor.

Könnt ihr mir helfen, wie ich das elegant und effizient lösen kann?

Gruß
Jens

 

[mermshaus]

Vielleicht lieber LEFT JOIN nehmen und bei leeren mastertable-Daten die Wiederholungsgruppen zur letzten Röhre hinzufügen?

Das verstehe ich so nicht. Die Beschreibung klingt eher nach RIGHT JOIN, also alle Einträge aus der Bilder- oder Links-Tabelle in der Ergebnismenge?

Die Frage nach der Art von Join ist im Grunde die Frage, welche Daten du geliefert bekommen möchtest.

• Alle Einträge aus mastertable? → LEFT JOIN
• Nur die Einträge aus mastertable, die ein Bild/einen Link gesetzt haben? → INNER JOIN

Ich würde die Abfrage von Links beziehungsweise Bildern in zwei unabhängigen Queries belassen (vermutlich im Rahmen einer Transaktion) oder – wenn es bequemer sein soll – auf einen ORM setzen wie Doctrine.

- Doctrine - PHP Object Persistence Libraries and More

 

[JensB]

Hallo,

naja, eigentlich möchte ich nachher in HTML-Ausgabe eine Tabelle haben, die diese Gestalt hat:

ID | Name | Alle Bilder | Alle Links

1 | Test1 | 01.jpg, 02.jpg, 03.jpg | link1
2 | name2 | asd.png | link1, link2
3 | name3 | - | l1, l2
4 | name4 | - | -

Wie ich das mit einer einzigen SQL-Query hinbekomme ist eigentlich die Frage.

ORM mit entsprechender Software ist eigentlich Overkill, das bekomme ich noch selber hin. Ich habe lieber alle Fäden selbst in der Hand.

Die wichtige Frage ist, wie ich das in einem Datenbankaufruf machen kann. Denn: Diese Elektronenröhren (das werden nachher einige hundert bis vielleicht tausend oder so werden) sollen ja möglichst schnell angezeigt werden, und ich möchte nicht wirklich bei jeder Wiederholungsgruppe der Röhren (da wären nachher noch neben Bildern und Links zum Beispiel Datenblätter, Hersteller, Vergleichstypen, etc...) eine extra Anfrage machen und dann linear jeweils die Liste à là "link1, link2, link3" mit jeweils einer while-Schleife zusammenbauen. Das kommt mir sehr umständlich vor.

Ich hoffe, mein Anliegen ist ein bisschen deutlicher geworden? ;-)

Danke für die Hilfe,
viele Grüße,
Jens

 

[achtelpetit]

Dein Stichwort heißt "mysql transponieren", diese Erklärung sieht gut aus: MySQL: Mit GROUP_CONCAT Zeilen in Spalten transponieren « Beckdoor.de
Das ganze könnte dann gut als View gespeichert werden.

 

[mermshaus]

Ich halte das nicht für sonderlich praktikabel, weil:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.

- MySQL :: MySQL 5.6 Reference Manual :: 11.16.1 GROUP BY (Aggregate) Functions

Das kann zu den wildesten Hacks führen.

und ich möchte nicht wirklich bei jeder Wiederholungsgruppe der Röhren (da wären nachher noch neben Bildern und Links zum Beispiel Datenblätter, Hersteller, Vergleichstypen, etc...) eine extra Anfrage machen und dann linear jeweils die Liste à là "link1, link2, link3" mit jeweils einer while-Schleife zusammenbauen. Das kommt mir sehr umständlich vor.

Du möchtest jede konkatenierte Spalte dann einzeln durch explode jagen (dabei hoffen, dass nichts zu Bruch geht, weil der Separator etwa im Inhalt eines Datums auch auftreten kann) und dann mit einer while-Schleife weiterverarbeiten?

ORM mit entsprechender Software ist eigentlich Overkill, das bekomme ich noch selber hin. Ich habe lieber alle Fäden selbst in der Hand.

ORM kann aber genau dieses Problem – für dich als Programmierer – elegant lösen. Stichwort in Doctrine wäre „fetch join“.

- 12. Doctrine Query Language — Doctrine 2 ORM v2.0.0 documentation

Intern dürfte Doctrine daraus je nach Anzahl der Wiederholungsgruppen eine mörderische Query mit einer Menge Joins machen. Deren Resultset kriegst du dann allerdings automatisch in eine verwendbare Datenstruktur gemappt. (Das erledigt praktisch das, was du als „sehr umständlich“ beschreibst.) Man kann darüber diskutieren, ich weiß. Ich wollte den Ansatz nur noch weiter vorstellen. In meiner Wahrnehmung handelt es sich dabei um eine Stärke von ORM beziehungsweise Doctrine. (Edit: Und ja, die Lernkurve ist leider recht steil. Ich kann es dennoch nur jedem, der hier mitliest, empfehlen, sich sowas mal intensiv anzusehen.)

Ich arbeite aus anderen Gründen generell gern mit Klassen, die Datensätze repräsentieren (müssen nicht persistierbar sein). Das hält die Anzahl der Klassen/Datenstrukturen gering, weil nicht für jedes Resultset eine eigene Datenstruktur eingeführt werden muss, und stellt außerdem sicher, dass ich an jeder Stelle des Codes an alle Werte des jeweiligen Objekts komme. Das ist gerade bei Erweiterung interessant. Erweitere ich die Klasse/die Generierung der Instanz um eine weitere Wiederholungsgruppe, habe ich in allem Code Zugriff auf die entsprechenden Werte. Spezialqueries, die nur das abholen, „was wirklich gebraucht wird“, generieren da doch gerne eine Menge Redundanz, da irgendwann an vielen Stellen dieselben Werte gebraucht werden, was schwierig zentral zu implementieren ist. (Und wenn man anfängt, es unredundant implementieren zu wollen, will man nichts weiter als Doctrine. Kein Witz. ;))

Welche Optimierungsstrategien (vor allem: Caching) du anwenden kannst, ist ohnehin noch mal eine andere Frage.

Fazit: Wie du dir die Daten letztlich beschaffst, ist eigentlich egal. Ich empfehle aber, das aus Gründen der Wartbarkeit möglichst zentral an einer Stelle zu erledigen. Optimieren oder umbauen kannst du dann dort nach Bedarf immer noch.

 

[JensB]

Danke für eure Antworten. Dieses GROUP_CONCAT ist schon ganz nett, aber nicht wirklich zielführend wegen der Zeichenbegrenzung. Also:

Ich habe Röhren, diesen können jeweils beliebig viele Links und Bilder hinzugefügt werden. Wie die Tabellen aussehen, steht oben. Ich möchte nun folgende Tabelle via HTML ausgegeben bekommen:

ID | Name | Alle Bilder | Alle Links
 1 | Test1 | 01.jpg, 02.jpg, 03.jpg | link1
 2 | name2 | asd.png | link1, link2
 3 | name3 | - | l1, l2
 4 | name4 | - | -

Nun gibt es zwei Möglichkeiten, die sich mir erschließen (Tabellennamen siehe oben):

1. Möglichkeit; ich mache so etwas:

$query = "SELECT * FROM masterdata ORDER BY id";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result)) {

  // und hier eben die Listen selber erstellen:
  $list_image_query = "SELECT * FROM roehren_bilder WHERE roehren_id = $row->id";
  $r = mysql_query(
  $list = "";
  while ($i = mysql_fetch_row) {
    $list .= ($list == "") ? "" : ", " . '<a href="' . $i->image . '">' . $i->image . '</a>';
  }

  // ähnlich für die Links

}

Das heißt pro Mastertabellenergebnis neue Queries. Mit GROUP_CONCAT geht es wegen der Links in der Liste zum Beispiel schonmal nicht.

2. Möglichkeit: Ich joine die Tabellen (wie auch immer) ganz zu Beginn einmal und hole mir daraus die Daten (umständlicher zu programmieren). Dann spare ich mir die weiteren Queries später. Effizienter?

3. Möglichkeit: Das muss doch auch einfacher mit SQL-Hausmitteln gehen?

Gruß
Jens

 

[achtelpetit]

Zur Klarstellung: ich weiß nichts von "Doctrine", wenn mermshaus sagt, daß das besser geeignet ist, würde ich das unbedingt prüfen.
Trotzdem das vollständige Zitat aus der MySQL-Doku:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet

...und unter max_allowed_packet steht dann

The protocol limit for max_allowed_packet is 1GB

Nebenbei: wozu soll das gut sein, alles in 1 Zeile zu packen, wenn 1024 Zeichen überschritten werden? Das kann doch kein Mensch lesen, und für die maschinelle Verarbeitung ist es nur ungünstig.
Da würde ich dem Betrachter zuliebe doch kleine, überschaubare, sinnvoll gegliederte Portionen darstellen.

 

[mermshaus]

...und unter max_allowed_packet steht dann

Da bin ich überfragt. Bei mir lokal wie auch auf meinem Webspace steht group_concat_max_len jedenfalls auf 1 KiB. max_allowed_packet steht bei beiden Systemen auf 16 MiB.

Ob sich group_concat_max_len zur Laufzeit ohne „echte“ Administrationsrechte anpassen lässt, kann ich nicht sagen. Da bin ich in meiner Einschätzung aber eher konservativ.

Das soll allerdings nicht heißen, dass es vielleicht doch die beste Lösung wäre. Siehe unten. Hm.

Zur Klarstellung: ich weiß nichts von "Doctrine", wenn mermshaus sagt, daß das besser geeignet ist, würde ich das unbedingt prüfen.

Na ja, „besser“ ist so eine Sache. Ich habe ja gesagt, dass man darüber diskutieren kann. Ich werde dazu morgen mal rumfragen, wenn ich dran denke.

Wahrscheinlich ist es aber günstiger, wenn wir das Thema erst mal zurückstellen. Ich glaube, das passt viel weniger gut, als ich ursprünglich dachte, weil das Grundproblem erst mal ein anderes ist.

[…]

Ich habe mal ein Gedankenspiel gemacht. Wenn du 1000 Röhren hast, mit denen im Schnitt 5 Links, 10 Bilder, 4 Dokumente, 2 Videos und 3 Kundenmeinungen verknüpft sind…

Möglichkeit 1: 5000 Queries mit insgesamt 1000*(5+10+4+2+3) = 24.000 Zeilen.
Möglichkeit 2: 1 Query mit insgesamt 1000*5*10*4*2*3 = 1.200.000 Zeilen. Jede dieser Zeilen hat außerdem enorm viele Spalten.
Möglichkeit 3: –
Möglichkeit 4: Jede verknüpfte Tabelle einzeln auf die Haupttabelle joinen: 5 Queries mit insgesamt 1000*5 + 1000*10 + 1000*4 + 1000*2 + 1000*3 = 24.000 Zeilen.

(Bei den Möglichkeiten 1 und 4 ist vermutlich noch je eine Query mit 1000 Zeilen zu ergänzen, um die Daten aus der Röhren-Tabelle zu bekommen.)

Nach dieser Darstellung ist Möglichkeit 4 die klar beste.

 

[JensB]

[...]

Möglichkeit 4: Jede verknüpfte Tabelle einzeln auf die Haupttabelle joinen: 5 Queries mit insgesamt 1000*5 + 1000*10 + 1000*4 + 1000*2 + 1000*3 = 24.000 Zeilen.

(Bei den Möglichkeiten 1 und 4 ist vermutlich noch je eine Query mit 1000 Zeilen zu ergänzen, um die Daten aus der Röhren-Tabelle zu bekommen.)

Nach dieser Darstellung ist Möglichkeit 4 die klar beste.

Na gut, so habe ich es bis jetzt auch gelöst. Das GROUP_BY ist ein nettes Feature, aber wohl leider zu beschränkt, um es effizient nutzbar werden zu lassen.

Gruß
Jens