MySQL: komplexe MM Beziehung

[sb]

Hallo,

ich möchte mit PHP / MYSQL einen Veranstaltungskalender realisieren. Jedes Event kann an mehreren Locations stattfinden, und jede Location kann mehrere Events anbieten - oder denselben Event mehrmals. Dazu habe ich nun 3 Tabellen, die erste enthält Details zur Location, die zweite enthält Details zum Event und die dritte Tabelle verknüpft die beiden anderen und liefert gleichzeitig das Datum, an dem der Event stattfindet:

tbl_events(
 id,
 name
)
tbl_locations(
 id,
 name
)
tbl_programm(
 id,
 event_id,
 location_id
 datum
)

Nun hätte ich unter anderem als Ergebnis einer Datenbankabfrage gern eine Liste der folgenden Form:

Event 1: Location A, Location B
Event 2. Location A, Location C, Location D

Ich möchte also einerseits die Events gruppieren, andererseits zu jedem Event die entsprechenden Locations anzeigen. Leider gelingt es mir bisher nicht, dieses Ergebnis in einer eleganten und möglichst Ressourcen schonenden Abfrage zu erzielen. Mein Problem ist, dass bei einer Gruppierung der Events jeweils immer nur eine Location agezeigt wird - lasse ich die Gruppierung aber weg, habe ich ein und denselben Event mehrmals in der Liste. Deshalb behelfe ich mir momentan mit diesem Provisorium:

// 1. Anfrage liefert die Events ...
$query = mysql_query("
SELECT * FROM tbl_events, tbl_locations, tbl_programm
          WHERE tbl_programm.event_id = tbl_event.id
          AND tbl_programm.location_id = tbl_locations.id
          GROUP BY tbl_events.id")

while($ar = mysql_fetch_array($query))
    {
    // ... 2. Anfrage liefert für jeden Event die Locations
    $subquery = mysql_query("SELECT name FROM tbl_locations, tbl_events, tbl_programm
                 WHERE tbl_locations.id = tbl_programm.location_id
                 AND   tbl_events.id = tbl_programm.event_id
                 AND   tbl_events.id = '".$ar["tbl_events.id"]."'")

$location = "";
      while($ars = mysql_fetch_array($subquery))
      {
        $location-name .= $ars["name"].", ";
      }
    }
    // ... und hier wird das dann zusammen gebaut.

Das finde ich aber ziemlich unschön. Vor allem wird für jeden Event eine neue Datenbankabfrage gestartet. Gibt es eine Möglichkeit, die Abfrage zu optimieren und eleganter, v.a. Ressourcen schonender zu demselben Ergebnis zu kommen?

Danke für eure Geduld und Hilfe!
S.

[Vinzenz Mai]

Hallo S.

ich möchte mit PHP / MYSQL einen Veranstaltungskalender realisieren.

welche MySQL-Version steht Dir zur Verfügung. Die Leistungsfähigkeit von MySQL unterscheidet sich zwischen einzelnen Versionen zum Teil dramatisch. Deswegen ist es bei MySQL-Fragen immer eine gute Idee, die vorhandene Version anzugeben.

Jedes Event kann an mehreren Locations stattfinden, und jede Location kann mehrere Events anbieten - oder denselben Event mehrmals.

tbl_events(
id,
name
)
tbl_locations(
id,
name
)
tbl_programm(
id,
event_id,
location_id
datum
)

Nun hätte ich unter anderem als Ergebnis einer Datenbankabfrage gern eine Liste der folgenden Form:

Event 1: Location A, Location B
Event 2. Location A, Location C, Location D

[...] PHP-Code [...]

Das finde ich aber ziemlich unschön. Vor allem wird für jeden Event eine neue Datenbankabfrage gestartet. Gibt es eine Möglichkeit, die Abfrage zu optimieren und eleganter, v.a. Ressourcen schonender zu demselben Ergebnis zu kommen?

Möglicherweise :-) Wenn Deine MySQL-Version die benötigte Funktionalität unterstützt.

Als erstes verbindest Du Deine drei Tabellen mit wunderschönen JOINS zu einer "Gesamttabelle". Falls Dir etwas dabei unklar sein sollte, lies bitte unsere Join-Artikel durch:

Einführung Joins
Fortgeschrittene Join-Techniken.

SELECT  
  e.name as Event,  
  l.name  
FROM tbl_events e  
INNER JOIN tbl_programm p  
ON e.id = p.event_id  
INNER JOIN tbl_locations l  
ON p.location_id = l.id  

Nun noch nach den Events gruppieren und die geeignete Aggregatsfunktion, nämlich GROUP_CONCAT() auf die Locations anwenden; beachte bitte die Hinweise in der Dokumentation zu dieser Funktion.

Folgender (ungetesteter) Code sollte Dir einen Einstieg in die Verwendung bieten:

SELECT  
  e.name as Event,  
  GROUP_CONCAT(  
    DISTINCT l.name  -- jede Location nur einmal  
    ORDER BY l.name  -- aufsteigend sortiert  
    SEPARATOR ', '   -- mit Komma und Leerzeichen getrennt  
  ) AS Locations     -- ein netter Spaltenname  
FROM tbl_events e  
INNER JOIN tbl_programm p  
ON e.id = p.event_id  
INNER JOIN tbl_locations l  
ON p.location_id = l.id  
GROUP BY e.name

Hättest Du das ganze gern aufsteigend nach dem ersten Datum eines Events sortiert, dann kommst Du um Subselects (Voraussetzung: MySQL 4.1 oder neuer) kaum herum.

Freundliche Grüße

Vinzenz

[sb]

Danke Vinzenz für dei ausführliche und nette Antwort, das sieht sehr gut aus, ich werde mich morgen damit beschäftigen und dann nochmal eine Rückmeldung geben. Meine MySQL Version ist 4.0.

S.

[Vinzenz Mai]

Hallo

Danke Vinzenz für dei ausführliche und nette Antwort, das sieht sehr gut aus, ich werde mich morgen damit beschäftigen

Hättest Du das ganze gern aufsteigend nach dem ersten Datum eines Events sortiert, dann kommst Du um Subselects (Voraussetzung: MySQL 4.1 oder neuer) kaum herum.

Kommt man doch: Normal mit ORDER BY sortieren, das war wohl zu einfach :-)

SELECT  
  e.name as Event,  
  GROUP_CONCAT(  
    DISTINCT l.name       -- jede Location nur einmal  
    ORDER BY l.name       -- aufsteigend sortiert  
    SEPARATOR ', '        -- mit Komma und Leerzeichen getrennt  
  ) AS Locations,         -- ein netter Spaltenname  
  MIN(p.datum) AS Beginn  -- erster Tag des Events  
FROM tbl_events e  
INNER JOIN tbl_programm p  
ON e.id = p.event_id  
INNER JOIN tbl_locations l  
ON p.location_id = l.id  
GROUP BY e.name  
ORDER BY Beginn           -- und nach dem ersten Tag sortieren  

und dann nochmal eine Rückmeldung geben. Meine MySQL Version ist 4.0.

Schade, die Doku sagt: GROUP_CONCAT was added in MySQL 4.1 :-(

Mein Vorschlag: Verwende folgendes Statement:

  
SELECT  
  e.name as Event,  
  p.datum,  
  l.location  
FROM tbl_events e  
INNER JOIN tbl_programm p  
ON e.id = p.event_id  
INNER JOIN tbl_locations l  
ON p.location_id = l.id  
ORDER BY e.name, l.location, p.datum  

Durchlaufe die Ergebnismenge mit PHP, führe bei Änderungen von "Event" einen Gruppenwechsel (Stichwort für Archivsuche) durch. Zu jeder Gruppe ermittle über entsprechende Arrays die Liste der Locations (auch hier könntest Du mit Gruppenwechsel arbeiten) und das kleinste Datum (falls überhaupt benötigt).

Ein hübsches Beispiel dafür, was den Unterschied zwischen 4.0.x und 4.1.x betrifft.

Freundliche Grüße

Vinzenz

[Ilja]

yo,

diese group_concat aggregat-funktion muss ich mir merken, habe bis jetzt noch wenig damit gearbeitet, sehr nützliche sache. bei einem ersten überfliegen habe ich das bei oracle noch nicht gesehen. diese mysql erweißt sich doch ab und zu als kleine goldgrube. bei oracle könnte man es ähnlich mit der sqlplus anweisung break lösen, wobei das nicht ganz das gleiche ist.

Ein hübsches Beispiel dafür, was den Unterschied zwischen 4.0.x und 4.1.x betrifft.

eigentlich hätte der umstieg von 4.0 auf 4.1 mehr verdient als nur eine ziffer hinter den komma, da sich wirklich viel verändert hat....

Ilja

[sb]

Guten Morgen!

Mein Vorschlag: Verwende folgendes Statement:

Alternative:  Mach' ein Update... :-) Ich habe mich für diese Lösung entschieden und dein Statement funktioniert auf Anhieb!
DANKE!

S.

[sb]

Das GROUP_CONCAT Statement funktioniert aber nur, wenn die Ausgabe eine reine Liste sein soll. Möchte ich z.B. die Locations zusätzlich verlinken (z.B. mit der Location-ID als Linkziel), muss ich doch auf eine andere Lösung zurückgreifen...

Gruß, S.

[Ilja]

yo,

Das GROUP_CONCAT Statement funktioniert aber nur, wenn die Ausgabe eine reine Liste sein soll. Möchte ich z.B. die Locations zusätzlich verlinken (z.B. mit der Location-ID als Linkziel), muss ich doch auf eine andere Lösung zurückgreifen...

genau, weil aggregat-funktionen per definition nur einen wert zurück liefern können. willst du es verlinken, dann geht eine abfrage über den join und den rest per php lösen.

Ilja

[Ilja]

yo,

nachtrag, php bietet sehr nützliche funktionen wie explode, mit dem du den einen wert durch ein zeichen getrennt sehr gut wieder aufsplitten kannst. also das update auf 4.1+ lohnt isch auf jeden fall und den rest machst du mit php.

Ilja

[sb]

nachtrag, php bietet sehr nützliche funktionen wie explode...

...und exakt damit habe ich es gemacht!
Gruß, S.