mySQL: Per Textfeld, die ID suchen (2x in einer Abfrage?)

[mgutt]

Ich habe eine Tabelle mit folgendem Inhalt:

id | titel1 | titel2 | titel3
1 | marc | michaela | ralf
2 | ralf | tina | torben
3 | tina | alexa | sven
4 | michaela | anna | thorsten

Folgendes Ziel:

- alle Zeilen ausgeben (SELECT * FROM ****)
- für titel2 und titel3 die ID suchen

Das Ergebnis könnte dann vielleicht so aussehen:

id | titel1 | titel2 | titel2id | titel3 | titel3id
1 | marc | michaela | 4 | ralf | 2
2 | ralf | tina | 3 | torben | 234
3 | tina | alexa | 22 | sven | 42
4 | michaela | anna | 233 | thorsten | 456

Ich habe es mal getestet in dem ich mit dem Ergebnis in einer While-Schleife jedesmal eine erneute Datenbankabfrage gestaltet habe und damit den Wert ausgelesen habe. Das macht dann aber eine Abfrage für die Liste und pro Zeile zwei zusätzliche Abfragen für die IDs.

Das ist doch recht langsam, besonders wenn man versucht eine Pyramide darzustellen.

[PhilippK]

Das ganze mal mit der ersten Verknüpfen:

Code: Alles auswählen

SELECT t1.titel1, t1.titel2, t2.id as titel2id
FROM table t1
LEFT JOIN table t2 ON t1.titel2 = t2.titel1

Aber wirklich sauber ist deine Ausgangstabelle nicht wirklich
http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)

Gruß, Philipp

[mgutt]

Man kann sich das wie einen Stammbaum vorstellen. Du meinst es jetzt so, dass da nicht der Name des Vater oder der Mutter steht, sondern die ID des Vaters bzw. die ID der Mutter, richtig? Dann müsste ich erst die Daten einmalig ändern. Das Problem ist dabei nur, dass manche Titel keinen eigenen Datensatz, also eine Reihe besitzen.

Den Namen hinter der ID auszulesen geht dann schneller als so, wie ich es jetzt will oder welchen Vorteil bringt mir das?

EDIT: Ok, ich muss sie wohl vorher normalisieren, denn er lädt bis zur Vergasung und gibt mir kein Ergebnis aus

[mgutt]

Hi,

also mit dem Problem komme ich im Moment nicht weiter.

Sagen wir die Datenbank wäre so aufgebaut:
ID | Name | VaterID | MutterID

Wie ermittel ich denn am besten die Daten bei einem Stammbaum, der z.B. über 5 Generationen geht.

Weil ich habe ja erst nur die Kind-ID. Mit der lese ich die Eltern-IDs aus.. eine Abfrage.. dann lese ich an Hand der Eltern-IDs die Großeltern-IDs aus.. zwei weitere Abfragen.. usw.

Das ist ja Wahnsinn da ich damit bei jedem Aufbau jede Generation doppelte Abfragen erzeuge... also bei 5 Generation:
1 -> 2 -> 4 -> 8 -> 16 = 31 Datenbankabfragen

Mehr als fünf Generationen wären dann eine richtige Performancebremse. Wenn das nicht anders geht muss ich mir echt überlegen die Ergebnisse irgendwie zu cachen.

Bringt es was die Datenbankabfragen zu verschachteln?

Gruß

[S2B]

Entweder einmal alles auslesen und dann per Rekursion auswerten oder die Sache mit der left_id/right_id aus phpBB3.

[mgutt]

Alles geht nicht. Sind insgesamt 60.000 Kinder und mehr.

Den phpBB3-Weg kenne ich nicht. Gibt es dazu Dokumentationen?

Gruß

[Dr.Death]

Hi,

die phpBB3 Funktion liegt in der /includes/function_module.php

[Olli Oberhausen]

vielleicht hilft dir folgender Link zu Nested Sets etwas. http://www.klempert.de/nested_sets/artikel/

Die wahrscheinlichst eleganteste Methode theoretisch unendlich tiefe Baumstrukturen zu gestalten.

Gruß, Olli

[mgutt]

@ Olli

Danke, das ist echt interessant. Ich vergebe also jedem Eintrag die lft und rgt ID und habe damit Anzahl der eigentlichen Einträge mal 2. Sobald ich z.B. den 2.000sten Wert ändere gebe ich mit dem UPDATE an, dass er bei denen lft und rgt um 2 erhöhen soll welche nach diesem 2000sten Wert folgen.

Das der Updatebefehl schnell ist verstehe ich, aber ist er auch noch schnell, wenn häufig im unteren Bereich Änderungen vorgenommen werden und die Anzahl aller Werte z.B. 1.000.000 ist? Oder rechnet man dann mit MinusIDs? Das wäre ja dann im Endeffekt sinnvoll, da man dann im bis zu 0-49,99 % Fall für die Minusmethode entscheidet und im 50,01%-100% Fall für die Plusmethode.

Weil er muss ja sonst alles was höher ist als X immer wieder anpassen.

Aber vor allen Dingen.. wie filtere ich jetzt meinen Stammbaum? Ich meine ich muss ja jetzt erstmal herausfinden, wer der "älteste" Eintrag ist. Dann dessen Erben finden und das komplett als Baum berechnen lassen bevor ich klar die lft und rgt zuweisen kann

Gibt es ein Updatescript von phpBB2 auf auf phpBB3, dann könnte ich da ja schauen, wie man ca. lösen kann?


EDIT zur Performance:
habe das gelesen auf der Seite aus dem Link:

Dennoch muss an dieser Stelle zugunsten anderer Datenbankmodelle für Baumstrukturen eines festgehalten werden: Das Einfügen von Knoten ist der wunde Punkt der Nested Sets. Denn im schlimmsten Fall müssen bei der Einfügoperation die LFT-/RGT-Werte aller bestehenden Knoten verändert werden. Dies wirkt sich natürlich insbesondere bei sehr komplexen Bäumen negativ auf die Performance aus.

Das tangiert mich aber nicht sonderlich, da das hinzufügen der Einträge eh manuell gesteuert wird. D.h. Einträge werden moderiert aufgenommen oder eben nicht. Und das könnte man im Extremfall dann immer noch einmal am Tag per Cronjob abarbeiten lassen.

EDIT zum importieren der alten Einträge:
Damit bekomme ich ja eigentlich alle Einträge richtig in die Datenbank mit lft und rgt: (Ich muss das ja wegen dem Stammbaum gedanklich auf den Kopf stellen)

Code: Alles auswählen

SELECT COUNT... wie viele Einträge sind vorhanden
WHILE-Schleife.. für jeden Eintrag eine
SELECT $RGT und $LFT des Kindes bzw. der Kinder
UPDATE tree SET rgt=rgt+2 WHERE rgt >= $RGT;
UPDATE tree SET lft=lft+2 WHERE lft > $RGT;

ein Kind vorhanden:

Code: Alles auswählen

INSERT INTO tree (name,lft,rgt) VALUES ('Name des Kindes', $RGT, $RGT +1);

alternativ mehrere Kinder:
???

Als letztes folgt der nächste Eintrag in der WHILE-Schleife usw. bis alle Einträge abgearbeitet wurden.

D.h. beim Import verwende ich dann so viele INSERTs wie Kinder vorhanden sind, um den richtigen lft und rgt zu ermitteln. Mal im Kopf durchgehen.. klappt das auch, wenn kein Kind vorhanden ist, also ein Level 0 Kind ausgelesen wurde......



EDIT:
Irgendwie scheint das gar nicht zu gehen.. ich habe mal einen Minidatensatz aus dem Stammbaum aufgemalt:
[ externes Bild ]
Oben sind drei Kd von Mt & Vt, wiederrum ist Mt die Schwester von Kd und Tochter von Mt & Vt. Parallel dazu ist der Vt Einzelkind von Mt & Vt.

Ich sehe da irgendwie keine Möglichkeit mit lft und rgt zu arbeiten, da keine Baumstruktur in der Datenbank ist. Klar bei der Ausgabe der Daten hat man einen Baum, aber die Daten selber bilden keinen.

[gn#36]

Wie wäre es mit einer Struktur wie dieser:

Für Jeden Knoten wird eine ID festgelegt, außerdem ein Vater und eine Mutter. Zusätzlich gibt es eine KindWurzel, ein NaechsteGeschwister und ein VorherigesGeschwister und eine GeschwisterWurzel.

In einem solchen Baum kommt man sehr schnell von den Eltern zu beliebigen Kindern und deren Kindern, denn alle Kinder haben die KindWurzel der Eltern als GeschwisterWurzel. Die andere Richtung ist allerdings sehr langsam.

Eine andere Variante wäre eine neue Tabelle anzulegen in der die Verknüpfungen gespeichert werden, während die Daten selbst in der eigentlichen Tabelle enthalten sind, also z.B. eine Tabelle mit

Code: Alles auswählen

Knoten1 | Knoten2 | Relation

Dort könnte man direkt speichern dass Knoten "Karl" der Ur-Ur-Urgroßvater von "Heinz" ist und kann alle Verwandschaftsbeziehungen eines Knotens direkt aus einer einzigen Tabelle abrufen, allerdings wird diese Tabelle dann extrem groß (theoretisch wenn alle Knoten verwandt sind bei n Knoten für jeden Knoten n-1 Verbindungen, also n*(n-1) Einträge) außerdem wird es wiederum kompliziert die Verwandschaftsbeziehungen der Verwandten auszuwerten.

Vielleicht kann man das auch kombinieren und die Vorteile von beiden Versionen nutzen.