mysql-Abfrage mit Zuordnungstabelle

[Hotschi]

Hallo Leute,

vermutlich ein Standardproblem, das man sauberer lösen kann, als ich es mache. Mir geht es im folgenden darum, meinen Code zu optimieren und ggf. etwas dazuzulernen.

Also folgendes mysql-Setting: Ich habe eine Tabelle "stuff" in der Informationen über CDs gespeichert sind, eine Tabelle "songs", in der Informationen über Lieder gespeichert sind, und eine Zuordnungstabelle "stuff_nn_songs", in der den CDs die auf ihnen enthaltenen Lieder zugeordnet werden. (CDs können mehrere Lieder haben und Lieder können auf mehreren CDs enthalten sein.)

Erreichen will ich eine Ausgabe im folgenden Stil:

CD1: Lied1, Lied2, Lied3
CD2: Lied4, Lied5, Lied6,
etc.

Dazu habe ich folgende (vermutlich sehr schlechte?) Abfrage geschrieben:

Code: Alles auswählen

$sql = "SELECT st.id, st.name, st. release_date, st. releases, st.medium, st.special, st.pic, so.song_name
	FROM stuff st

	LEFT JOIN stuff_nn_songs nn
	ON st.id = nn.stuff_id

	LEFT JOIN songs so
	ON so.song_id = nn.song_id;"

$result = mysql_query($sql);

Das Problem an dieser Abfrage scheint mir zu sein, dass ich für jedes Lied eine Zeile zurückbekomme, in der sich die Informationen über die CD immer wiederholen. D.h. mit folgendem Script erhalte ich für das obige Beispiel 6 statt 2 Zeilen.

Code: Alles auswählen

$i = 0;
while($row = mysql_fetch_array($result))
	{
	$i++;
	$some_array[$i]['name'] = stripslashes($row['name']);
	$some_array[$i]['song_name'] = stripslashes($row['song_name']);
	$some_array[$i]['release_date'] = stripslashes($row['release_date']);
	$some_array[$i]['releases'] = stripslashes($row['releases']);
	$some_array[$i]['medium'] = stripslashes($row['medium']);
	$some_array[$i]['special'] = stripslashes($row['special']);
	$some_array[$i]['pic'] = stripslashes($row['pic']);
	}

Jetzt könnte ich die doppelten Zeilen natürlich per rausfiltern, indem ich mit php das Array anders gestalte:

Code: Alles auswählen

$i = 0;
while($row = mysql_fetch_array($result))
	{
	$i++;
	$some_array[$row['id']]['name'] = stripslashes($row['name']);
	$some_array[$row['id']]['song_name'][] = stripslashes($row['song_name']);
	$some_array[$row['id']]['release_date'] = stripslashes($row['release_date']);
	$some_array[$row['id']]['releases'] = stripslashes($row['releases']);
	$some_array[$row['id']]['medium'] = stripslashes($row['medium']);
	$some_array[$row['id']]['special'] = stripslashes($row['special']);
	$some_array[$row['id']]['pic'] = stripslashes($row['pic']);
	}

Damit habe ich das Array zwar so, wie ich es haben will, aber das scheint mir eine etwas unsaubere Lösung zu sein, eben weil mysql ja eine Menge Informationen doppelt zurückgibt. Und mit der Schleife überschreibe ich dabei die Variablen, die die Informationen über die CD enthalten ja ab dem zweiten Stück sinnloserweise immer mit denselben Werten.

Weil ich ähnliche Abfragen öfters mal habe, wäre ich für Tipps dankbar, wie man sowas (mit mysql?) besser hinbekommt.

Danke
Christoph

[Miriam]

Was mir auffällt, ist, daß Du zwar die Tabelle stuff_nn_songs angliederst, aber garkeine Spalten dieser Tabelle ansprichst.
Vllt. wäre es sinnvoll, mit FOREIGN KEYs zwischen den referenzierten Tabellen zu arbeiten.

Wenn Du mehrere identische Antworten erhältst, kannst Du das ggf. mit SELECT DISTINCT abfangen.

[Hotschi]

Hallo Miriam,

danke für Deine Antwort - die ich leider nicht recht verstehe.

** Die stuff_nn_songs brauche ich ja für den Joint.

** Ich bekomme ja nicht mehrere identische Antworten, sondern identische Felder innerhalb der verschiedenen Zeilen.Um im obigen Beispiel zu bleiben:

- CD1-Name, CD1-Veröffentlichungsdatung, CD1-Medium, (...), Lied1
- CD1-Name, CD1-Veröffentlichungsdatung, CD1-Medium, (...), Lied2
- CD1-Name, CD1-Veröffentlichungsdatung, CD1-Medium, (...), Lied3
- CD2-Name, CD2-Veröffentlichungsdatung, CD2-Medium, (...), Lied4
- CD2-Name, CD2-Veröffentlichungsdatung, CD2-Medium, (...), Lied5
- CD2-Name, CD2-Veröffentlichungsdatung, CD2-Medium, (...), Lied6

Mit dem zweiten php-Code kann ich zwar ein Array erzeugen, dass nur 2 statt 6 Werte (1. Ordung) hat, so dass ich keine Redundanzen mehr habe. Meine Frage ist, ob sich die mysql-Abfrage optimieren lässt, so dass ich von vornherein keine Redundanzen habe, die ich hinterher erst so umständlich rausfiltern muss.

Grüße
Christoph

[Miriam]

Zum 1. Problem:

$sql = "SELECT
st.id,
st.name,
st. release_date,
st. releases,
st.medium,
st.special,
st.pic,
so.song_name
FROM stuff st

LEFT JOIN stuff_nn_songs nn
ON st.id = nn.stuff_id

LEFT JOIN songs so
ON so.song_id = nn.song_id;"

Ich habe hier mal die Aliase hervorgehoben. Der SELECT spricht keine Spalten aus der Tabelle stuff_nn_songs an also brauchst Du sie auch nicht angliedern.

2.) Kannst ja mal einen Dump der Struktur Deiner Tabellen schicken. So aus dem Stehgreif kann ich dazu nichts sagen. Aber (My)SQL Abfragen können schon ne ganze Menge.

[Hotschi]

Okay, verstehe, hatte den Code etwas gekürzt. Im vollständigen Code hatte ich aus der Zuordnungstabelle noch die Liednummer ausgelesen.

Hier die (etwas vereinfachte) Datenbankstruktur:

Code: Alles auswählen

CREATE TABLE IF NOT EXISTS `songs` (
  `song_id` int(11) NOT NULL AUTO_INCREMENT,
  `song_name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`song_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `stuff`
--

CREATE TABLE IF NOT EXISTS `stuff` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `release_date` varchar(255) NOT NULL DEFAULT '',
  `medium` varchar(255) NOT NULL DEFAULT '',
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `stuff_nn_songs`
--

CREATE TABLE IF NOT EXISTS `stuff_nn_songs` (
  `stuff_id` int(11) NOT NULL,
  `song_id` int(11) NOT NULL,
  `track_nr` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[Pyramide]

Im Prinzip gibt es folgende Möglichkeiten, eine relationale Datenbank nach solchen Daten zu befragen:

1. Eine Abfrage auf die Elterntabelle und dann in einer Schleife für jeden zurückbekommenen Datensatz eine Abfrage auf die Kindtabelle.
   Vorteil: Code am einfachsten lesbar
   Nachteil: pro Eintrag in der Elterntabelle eine Datenbankabfrage; wird also bei mehr als einer Handvoll Datensätze in der Elterntabelle schnell immer langsamer.
2. Eine große Abfrage mit JOIN und dann die doppelt zurückgegebenen Werte aus der Elterntabelle ignorieren (also so, wie du es momentan machst)
   Vorteil: Nur eine einzige Datenbankabfrage
   Nachteil: Datensätze aus der Elterntabelle werden mehrfach übermittelt; wenn die Elterntabelle Spalten mit sehr großen Inhalten hat (text, blob, ...) und es sehr viele Einträge in der Kindtabelle gibt, verschwendet man also Netzwerkbandbreite.
3. Eine Abfrage auf die Elterntabelle und eine auf die Kindtabelle, welche alle Einträge für alle gesuchten Elternelemente zurückgibt. Dann in PHP die Daten zusammenbasteln.
   Vorteil: Nur zwei Datenbankabfragen
   Nachteil: Komplexer Code auf PHP-Seite erforderlich, um die Daten zusammenzubasteln. Je nach dem, wie die Daten aufgebaut sind und wie aufwändig man den Code schreibt, werden ggf. große Datenmengen temporär im Speicher benötigt.
4. Durch Datenbankfunktionen die Daten für das Resultset umwandeln/komprimieren und auf PHP-Seite wieder entpacken; z.B. die Datensätze der Kindtabelle mit group_concat (MySQL) bzw. listagg (Oracle) Kommasepariert ausgeben
   Vorteil: Nur eine einzige Datenbankabfrage
   Nachteil: Höhere Rechenleistung auf Datenbank erforderlich, ggf. komplexer Code auf PHP-Seite erforderlich, um die Daten wieder auseinanderzunehmen
5. Die Datenbankstruktur/Inhalte so ändern, dass die Abfrage vereinfacht werden kann (z.B. nested set bei phpBB in der Forentabelle oder Cache-Spalten in diversen Tabellen)
   Vorteil: Man kann komplett auf JOINs oder zusätzliche Abfragen verzichten
   Nachteil: Man schafft sich meistens Redundanzen in der Datenbank, muss also beim Ändern der Daten mehr Aufwand investieren und sich ggf. Synchronisierungsfunktionen zum Reparieren von kaputten Datensätzen bauen; dadurch höhere Komplexität des Codes.

Welche Abfrage die beste ist, hängt vom konkreten Fall ab. Man sollte anhand der zu erwartenden Datenmengen prüfen, wieviele und welche Resourcen (IO/CPU/RAM/Codekomplexität) bei welcher Variante als Overhead anfallen und dann entscheiden, welche sich am wenigsten negativ auswirken.

Bei einfachen Applikationen mit geringen Datenmengen und seltener Nutzung würde ich die Varianten A und B empfehlen, wobei ich Variante A nur bis maximal ca. 10 Datensätze in der Elterntabelle nehmen würde.

[Hotschi]

Hallo Pyramide,

vielen Dank für Deine ausführliche Antwort! Den konkreten Fall belasse ich dann wohl im Ansatz B. Für andere Probleme habe ich den Beitrag schon abgespeichert!

Grüße
Christoph