Multiple Joins über mehrere Tabellen

A

Aljoscha.Peters

Neuer Benutzer
Beiträge
3
Hi Leute,

ich arbeite gerade an meiner Abschlussarbeit. Diese basiert u.a. auf einer MySQL-Datenbank. Diese ist ordentlich normalisiert. Nun brauche ich eine Tabelle, die von mehreren Tabellen befüllt wird. Woran es scheitert, sind die JOINS. Da setzt mein Kopf gerade aus und deshalb baue ich auf Eure Hilfe. O:)

Ich habe 5 Tabellen: entities, keywords, names, entities_keywords, entities_authors; die sind wie folgt aufgebaut:

entities: id : int, title : text, ..., trash : tinyint
keywords: id : int, title : text, trash : tinyint
names: id : int, title : text, trash : tinyint
entities_keywords: entities_id : int, keywords_id : int (zusammengesetzter primary index aus entities_id und keywords_id)
entities_authors: entities_id : int, names_id : int, position : int

Die normalen Verknüpfungen (n-m-Relation) zur Anzeige funktionieren wunderbar. Updaten/Löschen ... alles super.

Jetzt brauche ich für die Realisierung der Suche folgendes:
Ich habe eine neue Tabelle erstellt (search_entities_authors_keywords), die erst einmal - um es zu vestehen - befüllt werden soll mit den Daten der m-n-Relationen von oben. Bisher bin nur erfolgreich bei Tabellen wie bspw. (search_entities_keywords), die dann folgendes machen:
INSERT `search_entities_keywords` (`id`, `title`, `year`,`keywords`
SELECT `e`.`id`, `e`.`title`,`e`.`year`, GROUP_CONCAT(`k`.`title` ORDER BY `k`.`title` SEPARATOR ',')
FROM
((`entities_keywords`
LEFT JOIN `keywords` `k` ON ((`entities_keywords`.`keywords_id` = `k`.`id`)))
LEFT JOIN `entities` `e` ON ((`entities_keywords`.`entities_id` = `e`.`id`)))
WHERE `e`.`trash` = 0 AND `k`.`trash` = 0
GROUP BY `slipbox`.`entities_keywords`.`entities_id`
ORDER BY `e`.`title` ASC

Mir ist also unklar, wie ich es anstellen muss, dass ich ALLE entities bekomme (mit e.trash=0), mit allen Keywords und Authors!

Ich muss ja eigentlich dann SELECT ... FROM entities nutzen mit mehreren JOINS über die Verknüpfungstabellen entities_keywords und entities_authors. Aber wie ich das mache, ist mir total schleierhaft! :(

Ich danke Euch im Voraus schon einmal gaaaaanz herzlich und wünsche weiterhin ein schönes Wochenende.
Aljoscha
 
Werbung:
Wozu benötigst Du eine neue Tabelle für die Suche? Da reicht eine Abfrage, oder ein VIEW.

Zeig Dein Problem anhand der Tabellendefinitionen, einigen Beispiel-Datensätzen und dem, was rauskommen soll.

Was ich schon sehe: Du aggregierst ohne zu gruppieren. Auch wenn MySQL für den Fehler blind ist: es ist ein Fehler.
 
Hi akretschmer!

Danke zuallererst für Dein Hilfsangebot! :)
Habe einen Teil der Tabellendefinitionen als UML angehängt. Hinzu kommen noch weitere Tabellen für Ideen, Sammlungen, Dateien, ... Wenn ich aber das Prinzip mit den JOINS für das Problem verstanden hab, komme ich mit dem Rest sicherlich allein klar. O:)

Vorweg:
Mein theoretisches Thema zur BA beschäftigt sich leider garnicht mit Information Retrieval oder Datenbanken, deshalb meine Unwissenheit.

Ich hatte im Netz aufgeschnappt, jetzt, wo sich das Problem der Suche stellt, dass Views nicht gecached werden. Ziel ist es aber gerade, eine möglichst schnelle Suche zu bekommen. Deshalb meine Idee: Da die Datensätze eher selten geändert werden, eher werden ab und an neue Hinzugefügt (Zettel- und Ideenkasten eines Profs.), einfach eine Tabelle (oder mehrere Tabellen), die gerade die spezifischen Suchanfragen des Profs. berücksichtigen. Zudem kann ich dann über die Tabelle(n je) eine FULLTEXT-Suche legen.

Zudem habe ich schon mit Views gespielt und ich fand die Geschwindigkeit nicht so pralle (Entitäten sind 100.000 Datensätze), dafür, dass ich solche "einfachen" Joins wie oben benutzte. Nutze die MySQL-Workbench.

Am Server selbst kann ich nicht all zu viel ändern, es wird ein einfacher XAMPP von Apachefriends mit einem Windows 7 sein. Wahrscheinlich sogar als Portable-Version. UI wird über PHP-Framework Phalcon realisiert.

Aber zurück zur anzulegenden Tabelle:
Aus den sechs Tabellen soll eine herauskommen mit den Spalten:
entities.id, entities.title, entities.subtitle, entities.year, group_concat(keywords.title SEPARATOR ',') - der jeweiligen Keywords, group_concat(names.title SEPARATOR ',') - der jeweiligen Namen, die als Autoren verwendet werden, group_concat(names.title SEPARATOR ',') - der jeweiligen Namen, die als Herausgeber (editors) verwendet werden.

Bsp.:
entities: (id = 1, title = 'Testentität', subtitle = NULL, year = 1970, ..., trash = 0)

entities_authors: (entities_id = 1, names_id = 1)
entities_editors: (entities_id = 1, names_id = 2), (entities_id = 1, names_id = 3)
entities_keywords (entities_id = 1, keywords_id = 1), (entities_id = 1, keywords_id = 2), (entities_id = 1, keywords_id = 3)

mit
names: (id = 1, title = 'Name 01', trash = 0), (id = 2, title = 'Deleted name', trash = 1), (id = 3, title = 'Name 03', trash = 0), ...
keywords: (id = 1, title = 'Keyword 01', trash = 0), (id = 2, title = 'Keyword 02', trash = 0), (id = 3, title = 'Deleted keyword', trash = 1), ...

Herauskommen soll in der Tabelle:
id = 1, title = Testentität, subtitle = NULL, year = 1970, keywords = 'Keyword 01,Keyword 02', authors = 'Name 01', editors = 'Name 03'

Da die Daten eher selten geändert werden, würde ich dann Trigger hinterlegen:
Wenn Entität erstellt wird, füge entsprechende Zeile ein.
Wenn Entität geändert wird, ändere entsprechende Zeile.
Wenn Entität gelöscht wird, löschen entsprechende Zeile.
Wenn Name geändert wird, ändere entsprechende Spalten.
Wenn Name gelöscht wird, ändere entsprechende Spalten.
...

Danke Dir nochmals!
 

Anhänge

  • slipbox-ausgesucht.png
    slipbox-ausgesucht.png
    94,1 KB · Aufrufe: 1
Um es mal ganz simpel zu zeigen:

Code: 
test=*# create table autor (id int primary key, name text);
CREATE TABLE   
Time: 26,757 ms   
test=*# create table buch (id int primary key, name text);
CREATE TABLE   
Time: 18,276 ms   
test=*# create table buch_autor (buch_id int references buch, autor_id int references autor, primary key(buch_id, autor_id));
CREATE TABLE   
Time: 81,758 ms   
test=*# insert into autor values (1, 'autor1');
INSERT 0 1   
Time: 0,434 ms   
test=*# insert into autor values (2, 'autor2');
INSERT 0 1   
Time: 0,200 ms   
test=*# insert into autor values (3, 'autor3');
INSERT 0 1   
Time: 0,192 ms   
test=*# insert into buch values (1, 'buch1');  
INSERT 0 1   
Time: 0,479 ms   
test=*# insert into buch values (2, 'buch2');
INSERT 0 1   
Time: 0,188 ms   
test=*# insert into buch_autor values (1,1);  
INSERT 0 1   
Time: 11,834 ms   
test=*# insert into buch_autor values (1,2);
INSERT 0 1
Time: 0,354 ms
test=*# insert into buch_autor values (1,3);
INSERT 0 1
Time: 0,310 ms
test=*# insert into buch_autor values (2,3);
INSERT 0 1
Time: 0,326 ms
test=*# insert into buch_autor values (2,2);
INSERT 0 1
Time: 0,368 ms
test=*# select b.name, array_agg(a.name) from buch_autor ab left join autor a on ab.autor_id=a.id left join buch b on ab.buch_id=b.id group by b.name;
 name  |  array_agg
-------+------------------------
 buch2 | {autor3,autor2}
 buch1 | {autor1,autor2,autor3}
(2 rows)

Das ist jetzt PostgtreSQL, das Resultat ein ARRAY.

Code: 
test=*# select b.name, array_to_string(array_agg(a.name),', ') from buch_autor ab left join autor a on ab.autor_id=a.id left join buch b on ab.buch_id=b.id group by b.name;
 name  |  array_to_string
-------+------------------------
 buch2 | autor3, autor2
 buch1 | autor1, autor2, autor3
(2 rows)

So ists besser. Wichtig ist das GROUP BY, ich denke, das Prinzip eines JOIN's sollte erkennbar sein.

Views und lahm: richtige Datenbanken haben materialized views. Du hast MySQL. Das hat wohl auch keine so schnellen Joins. Kann man machen mit triggern, ist aber ein weiteres Feld von Fehlern.
 
Hey, danke für die Mühe!

Einen einmaligen JOIN kriege ich hin, auch mit GROUP BY mache ich das. Woran ich scheitere - da bin ich wahrscheinlich einfach zu blöd bin: Ich will ja gerade in einer Tabelle Entitäten-ID, Entitäten-TITEL und Keywords und Autoren und Herausgeber. Da muss ich doch bestimmt erst andere JOINS machen, mir ist aber unklar, welche und in welcher Reihenfolge. :/ Oder muss ich sogar mit Subqueries arbeiten?!

Danke Dir
 
Hallo, hallo....

Danke für die Beispiele und die Erläuterungen!
Ich bin kein Datenbankexperte (wie unschwer zu erahnen ist ;-) ), hab das aber mal mit meinen Tabellen und in MySQL nachgebaut (und sogar ein bisschen verstanden).

Allerdings stolpere ich (natürlich, da PostgreeSQL) über
array_to_string(array_agg(a.name),', ')

Wie müsste das denn für MySQL heißen?
Ich finde im Netz (wahrscheinlich dank unklarer Suchen) leider keinen nachvollziehbaren Tipp.


VG, Norbert
 
Werbung:

Ähnliche Themen

K
MySQL: Best Practice bei Views zu n:m-Beziehungen
Antworten
2
Aufrufe
2K
Kampfzwerg
K
E
Effizient in mehreren Tabellen und Spalten suchen
Antworten
1
Aufrufe
3K
ElTorro
E
S
Suche Hilfe, Select über mehrer Tabellen mit Join und SUM * und +
Antworten
11
Aufrufe
3K
Steini
S
Zurück
Oben