Vorfahren aus Datenbank rekursiv ermitteln?

[Linuchs]

Moin,

die Vorfahren einer Person ermittle ich durch viele JOIN, bis zu den Urgroßeltern sind das schon 14 (2+4+8) in verschiedenen Spalten eines Treffers.

  $q = "
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Person, Eltern, Großeltern lesen zum Anzeigen
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT

 per8.id            mu_mu_mu_id
,per8.geb_name      mu_mu_mu_geb_name
,per8.vor_name      mu_mu_mu_vor_name
,per8.beruf         mu_mu_mu_beruf
,per8.geb_datum     mu_mu_mu_geb_datum
,per8.geb_ort       mu_mu_mu_geb_ort
,per8.sterbe_datum  mu_mu_mu_sterbe_datum
,per8.sterbe_ort    mu_mu_mu_sterbe_ort
,TIMESTAMPDIFF( YEAR, per8.geb_datum, IF( per8.sterbe_datum IS NULL, CURDATE(), per8.sterbe_datum )) mu_mu_mu_alter
...
-- mu_mu_mu
LEFT JOIN ".$db[0]['personen']." per8
ON        per8.id = per4.mutter_id

Ich suche nach einer Möglichkeit, die Personen einzeln zu bekommen . Wie könnte das per SQL gehen? Rekursiv?

ich habe bisher diese Struktur:

Gruß, Linuchs

[MudGuard]

Hi,

die Vorfahren einer Person ermittle ich durch viele JOIN, bis zu den Urgroßeltern sind das schon 14 (2+4+8) in verschiedenen Spalten eines Treffers.

Ich suche nach einer Möglichkeit, die Personen einzeln zu bekommen . Wie könnte das per SQL gehen? Rekursiv?

Dazu würde sich die Struktur B-Baum anbieten.

Ohne Änderung des Datenmodells wird das eher nix.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Linuchs,

Meine DB ist MySQL-Vers.=[10.1.37-MariaDB-0+deb9u1

welches DBMS, welche Version genau? Hab ich Dich bestimmt schon 17 mal gefragt, aber ich habe kein Dossier für jeden Forenteilnehmer 😉. Und ein schlechtes Gedächtnis.

Das geht mit rekursiven Common Table Expressions. Du musst auf Maria 10.2 oder höher updaten, damit die möglich sind.

Ohne rekursive CTE bleibt Dir nur die Joinerei - oder Du musst pro Generation eine neue Query starten und jeweils die Liste der Eltern-IDs aus der vorigen Generation als IN-Klausel reingeben.

Was sollen übrigens die varchars für Vater und Mutter? Soll das eine Alternative sein für den Fall, dass Du die Eltern nur namentlich hast, aber nicht als Person mit ID?

Damit Du weißt, was Dich erwartet: die Query mit rekursiven CTEs sieht in MariaDB so aus. Sie ist eeeetwas komplizierter, weil Du zwei CTEs brauchst und die Ergebnisse kombinieren musst, einmal in Elternrichtung und einmal in Kinderrichtung. Andernfalls gibt's eine Endlosschleife (zumindest bei mir gerade, in einer Testtabelle).

Die magische Zahl 4, die da an 3 Stellen auftaucht, ist die ID der Bezugsperson, hier im Beispiel Herzog Max in Bayern.

WITH RECURSIVE 
   Eltern (stufe, id, vater_id, mutter_id,
           vor_name, geb_name, geb_datum, sterbe_datum) 
   AS (
      SELECT 0 AS stufe, id, vater_id, mutter_id,
             vor_name, geb_name, geb_datum, sterbe_datum
      FROM stammbaum
      WHERE id=4
     UNION ALL
      SELECT e.stufe-1, s.id, s.vater_id, s.mutter_id,
             s.vor_name, s.geb_name, s.geb_datum, s.sterbe_datum
      FROM stammbaum s JOIN Eltern e
           ON s.id = e.vater_id OR s.id = e.mutter_id
      WHERE e.stufe > -2
   ),
   Kinder (stufe, id, vater_id, mutter_id, 
           vor_name, geb_name, geb_datum, sterbe_datum) 
   AS (
       SELECT 1 AS stufe, id, vater_id, mutter_id,
              vor_name, geb_name, geb_datum, sterbe_datum
       FROM stammbaum
       WHERE vater_id = 4 OR mutter_id = 4
     UNION ALL
       SELECT k.stufe+1, s.id, s.vater_id, s.mutter_id,
             s.vor_name, s.geb_name, s.geb_datum, s.sterbe_datum
       FROM stammbaum s JOIN Kinder k
                         ON k.id = s.vater_id OR k.id = s.mutter_id
       WHERE k.stufe < 2
   )
SELECT * FROM Eltern
UNION 
SELECT * FROM Kinder
ORDER BY stufe

Damit krieg ich dann z.B. sowas raus:

Hab ich gerade den Stammbaum von Sissi in eine Table gekloppt? Ja. In der Tat 😂

Rolf

[Rolf B]

Hallo nochmal,

eine gute Erklärung von rekursiven CTEs findest Du übrigens hier bei Tante Maria. Leider nur auf englisch.

Rolf

[Linuchs]

Hallo Rolf,

welches DBMS, welche Version genau?

Keine Ahnung, wo sehe ich das? phpMyAdmin zeigt dieses:

varchars für Vater und Mutter? Soll das eine Alternative sein für den Fall, dass Du die Eltern nur namentlich hast, aber nicht als Person mit ID?

Genau. Wenn Eltern später angelegt werden, habe ich die id ja noch nicht und ergänze sie von Hand. Später soll Ajax beim Tippen der Namen Vorschläge anbieten.

Du musst auf Maria 10.2 oder höher updaten, damit die möglich sind.

Ich lasse mir ein Angebot kommen, um auf eine virtual machine umzusteigen. Die Miete enthält angeblich Updates, die ich bisher extra zahlen musste.

Du hast dir wieder viel Mühe gemacht, ich war inzwischen auch nicht untätig und ermittle die Vorfahren (noch nicht die Nachfahren) mit einer rekursiven PHP-Funktion

//=======================================================
  function liesMutterVaterId( $lfd, $id, $geschlecht ) {
//=======================================================
  global  $db, $owner_id, $conn_id, $csv_string;
  $q = "
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# name, mutter_id, vater_id
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
 per1.*
,TIMESTAMPDIFF( YEAR, per1.geb_datum, IF( per1.sterbe_datum IS NULL, CURDATE(), per1.sterbe_datum )) jahre_alt -- alter = geschuetztes Wort
FROM      ".$db[0]['personen']." per1
WHERE     per1.owner_id = '" . $owner_id . "'
AND       per1.id       = '" . addslashes( $id ) . "'
";
  $res_person = @mysql_query( $q, $conn_id ); zeigSqlFehler( $q, $conn_id );
  if ( $row_person = @mysql_fetch_assoc( $res_person )) {
//  echo $lfd . " id=[" . $id . "] name=[" . $row_person['geb_name'] . ", " . $row_person['vor_name'] . "] mutter_id=[" . $row_person['mutter_id'] . "] vater_id=[" . $row_person['vater_id'] . "]<br>";

    $csv_string
   .= $lfd                        .";"
    . $row_person['id']           .";"
    . $row_person['geb_name']     .";"
    . $row_person['vor_name']     .";"
    . $row_person['beruf']        .";"
    . $row_person['geb_datum']    .";"
    . $row_person['geb_ort']      .";"
    . $row_person['sterbe_datum'] .";"
    . $row_person['gestorben']    .";"
    . $row_person['sterbe_ort']   .";"
    . $row_person['jahre_alt']    .";"
    . $id                         .";"  // kind_id, Verweis auf Rechteck HTML-id
    . $geschlecht                       // w (Mutter) über dem Kind-Rechteck, m (Vater) unter dem Rechteck
    . "\n";

    // rekursiver Aufruf dieser Funktion
    if ( $row_person['vater_id'] ) {
      liesMutterVaterId( $lfd."- ", $row_person['vater_id'], "m" );
    }

    // rekursiver Aufruf dieser Funktion
    if ( $row_person['mutter_id'] ) {
      liesMutterVaterId( $lfd."- ", $row_person['mutter_id'], "w" );
    }
    return  $row_person;
  }
}

Den csv_string mit den gefundenen Ahnen übergebe ich javascript, um die Grafik zu erstellen. Das Geschlecht bestimmt, ob die Person oberhalb (Mutter) oder unterhalb (Vater) des Kindes angezeigt wird.

Ist aber noch in Arbeit und Test, kann sich noch ändern.

Gruß, Linuchs

[Rolf B]

Hallo Linuchs,

Du weißt sicher schon, was jetzt kommt. Ein Hinweis-Marathon...

Den csv_string mit den gefundenen Ahnen

Nimm JSON. Das Volumen ist nicht so groß, dass der gesparte Datenoverhead sich lohnt, und dafür musst Du keinen selbstgemachten CSV-Codierer und -Parser verwenden. Schreib nicht selbst, was fertig vorhanden ist. CSV ist auch nicht einfach Stringverkettung; du musst eventuell vorhandene CSV-Steuerzeichen in Strings maskieren.

"'" . addslashes($id) . "'"

Zweimal "wieso?". Deine ids sind in der DB int-Werte. D.h. die Query sollte auch Integers vergleichen, nicht Strings. Prüfe die ID in $_GET[] auf numerisch und weise sie bei Fehler ab. Danach konvertiere sie mit intval() in einen Integer und damit ist es gut. Ich würde Dir ja Type Hints für die Lesefunktion empfehlen wollen, aber das geht erst ab PHP 7. Du hast VIEL zum Updaten! Ohne Type Hints solltest Du an dieser Stelle einfach nochmal intval() setzen, das ist schnell fertig wenn es einen int als Argument bekommt.

SELECT per1.*

Nein. SELECT * verwendet man maximal in ad hoc Queries in phpMyAdmin, nicht in Programmen. Man führt die Spalten einzeln auf.

-- alter = geschuetztes Wort

Dafür gibt's die Backticks. Je nach gesetztem SQL_MODE (ANSI_QUOTES oder MSSQL) kannst Du auch die "Anführungszeichen" oder [Brackets] verwenden - aber idiomatisch für die MySQL-Welt sind Backticks.

SELECT   `select`, `as`, `order` 
FROM     `from` JOIN `left` ON `from`.`on`=`left`.`by`
WHERE    `and` = 42
ORDER BY `order`

Reservierte Wörter? Null Problemo.

@mysql_query

Whoaaa - mysql? Nicht nur PHP 5.6, sondern auch eine "verbotene" MySQL Schnittstelle? Verwende unbedingt mysqli oder PDO für neue Anwendungen.

Und das @ Präfix brauchst Du eigentlich nicht. Statt dessen solltest Du nach dem query-Aufruf $res_person === FALSE abfragen, in dem Fall ging die Query schief. Desgleichen für den fetch-Aufruf. Der kann ein Array sein, null bei Ende der Abfrage oder FALSE bei einem Error. Errors sollte man zumindest loggen.

liesMutterVaterId(...)

• Die Funktion liest keine IDs, sondern Eltern-Rows. Ich würde deshalb das Id aus dem Namen entfernen.
• Die Funktion sollte einen Parameter $stufe oder $generation bekommen, damit... ah moment. $lfd. Siehe unten.
• Rechne mit Ahnenschwund. Ehen unter Cousins können durchaus vorkommen, in dem Fall haben zwei Eltern das gleiche Großelternteil A. In dem Fall bekommst Du die Person zweimal in die Liste. Clientseitig wirst Du sie vermutlich in einem Array mit der ID als Index sammeln, d.h. Duplikate eliminieren sich dort dann sowieso. Ein Grund gegen die CSV-Formatierung.

liesMutterVaterId( $lfd . "-", ...)

Ist das dein Generationenzähler? Die Anzahl von Minuszeichen beschreibt die Generation? Tu das nicht, verwende eine Zahl. Die Frage, wie man diese Zahl dann visualisiert, ist Sache des UI. Z.B. mit "".padEnd(lfd, "-") (oder "-----".substr(0,lfd) für IE-Kompatibilität und max. 5 Generationen)

Aus Architektursicht würde ich die gefundenen Personen entweder in einem Array sammeln, einfach Generation, Kind-ID und Geschlecht von Hand in $row eintragen und dann ablegen in $persons[] = $row. Und am Ende schickst Du dieses Array mit json_encode($persons) an den Client.

Am Client verwendest Du JSON.parse(array, reviver) - reviver ist eine Funktion, die pro Wert im JSON-Objekt aufgerufen wird. Damit kannst Du die Datümer von YYYY-MM-DD gleich beim Deserialisieren in JavaScript-Date Objekte umwandeln. Oder in TT.MM.YYYY, wenn Du unbedingt willst 😉. Wie man den Reviver verwendet, hab ich vor 3 Jahren mal hier aufgeschrieben.

Ob Du die Kind-Id überhaupt brauchst? Das ist eine doppelte Verpointerung. Am Kind hast Du die ID von Vater und Mutter stehen. Bei Cousinenehen würdest Du den gemeinsamen Großelternteil dann zweimal in der Tabelle haben, mit unterschiedlichen Kind-IDs. Lohnt diese Redundanz?

An Stelle eines globalen Sammelarrays könntest Du auch einen funktionaleren Ansatz wählen und einfach ein Array der gefundenen Elternteile zurückgeben.

liesStammbaum($stufe, $id, $geschlecht, $db) {
   $row_person = ... // query;
   if (!$row_person) return FALSE;

   $vaterseite = liesStammbaum($stufe+1, $row['vater_id'], 'm', $db);
   if ($vaterseite === FALSE) {
      // Konstruiere Vater aus Namensangabe, als Array aus einer Row
   }
   $mutterseite = liesStammbaum($stufe+1, $row['mutter_id'], 'w', $db);
   if ($mutterseite === FALSE) {
      // Konstruiere Mutter aus Namensangabe, als Array aus einer Row
   }
   return array_merge( [ $row_person ], $vaterseite, $mutterseite);
}

Look Ma, no globals!

Rolf

[tk]

Hallo Rolf,

grundsätzlich stimme ich dir zu, ein paar Sachen würde ich nur gerne noch ergänzen bzw. verdeutlichen:

"'" . addslashes($id) . "'"

Zweimal "wieso?". Deine ids sind in der DB int-Werte. D.h. die Query sollte auch Integers vergleichen, nicht Strings. Prüfe die ID in $_GET[] auf numerisch und weise sie bei Fehler ab. Danach konvertiere sie mit intval() in einen Integer und damit ist es gut. Ich würde Dir ja Type Hints für die Lesefunktion empfehlen wollen, aber das geht erst ab PHP 7.

Das wird so nicht funktionieren: die Werte in $_GET (und auch $_POST) sind immer Strings, egal welcher Wert darin steht. Natürlich kann man mit ctype_digit() kontrollieren ob da ausschließlich Ziffern drin stehen – ich würde das aber alles die Datenbank machen lassen und prepared Statements verwenden.

Das eigentliche Problem von addslashes() ist aber ein ganz anderes: die Funktion ist nicht geeignet um den Kontextwechsel zu SQL korrekt zu behandeln, dafür gibt es spezielle Funktionen, bei mysqli ist das mysqli_real_escape_string() (bei der mysql-Erweiterung ohne das erste i).

@mysql_query

Whoaaa - mysql? Nicht nur PHP 5.6, sondern auch eine "verbotene" MySQL Schnittstelle? Verwende unbedingt mysqli oder PDO für neue Anwendungen.

Nicht nur für neue Anwendungen. PHP 5 (und damit auch mysql_query&Co) ist tot. Mausetot. Selbst PHP 7 hat nur noch rund 1 Jahr zu leben …
PHP 5 sollte man nicht mehr einsetzen, da gibt es seit bald drei Jahren keine Updates mehr und deutlich schneller geworden ist PHP ab Version 7 auch. Und ja, ich weiß dass das – je nach Code – u.U. einiges an Aufwand ist, aber mit einem guten Editor lassen sich die Funktionen relativ leicht durch mysqli ersetzen (eine Versionsverwaltung wie git verwenden um bei Problemen zu bisherigen Version zurück zu können!).

Zu aktualisieren gibt es allerdings noch mehr: Debian 9 wird zwar noch unterstützt, ist aber schon von 2018 und der LTS-Support endet im kommenden Juni. Damit zusammenhängen wird wohl die Tatsache das MariaDB 10.1 installiert ist, auch diese Version wird nicht mehr unterstützt. phpMyAdmin 4.6.6 ist von Anfang 2017, seitdem wurden schon einige Lücken geschlossen weswegen ein Einsatz dieser Version wenig sinnvoll ist (wobei ich generell eher zu Adminer raten würde).

Und das @ Präfix brauchst Du eigentlich nicht.

Das hatte ich die Woche auch schon erwähnt – nur leider ignoriert Linuchs jegliche Kritik an seinem Code völlig.

liesMutterVaterId(...)

[…]

neben dem bereits genannten (genannt wurde es aber nochmal zur Verdeutlichung): global ist böse™. Variablen die in einer Funktion benötigt werden sollten immer als Parameter übergeben werden, wenn da irgendwelche Variablen per global auftauchen führt das zu unlesbarem und unwartbarem Code.

Gruß
Tobias

[dedlfix]

Tach!

Das eigentliche Problem von addslashes() ist aber ein ganz anderes: die Funktion ist nicht geeignet um den Kontextwechsel zu SQL korrekt zu behandeln, dafür gibt es spezielle Funktionen, bei mysqli ist das mysqli_real_escape_string() (bei der mysql-Erweiterung ohne das erste i).

Ein Problem ist es nicht wirklich. Es gibt die genannten spezialisierten Funktionen, die man auch einsetzen sollte, wenn man aus irgendeinem Grund keine Prepared Statements verwendet. Aber addshlashes() behandelt alle für Strings in SQL-Statements relevanten Zeichen, wenn man eine ASCII-basierende Zeichenkodierung verwendet. Die weitergehende Funktionalität der anderen Funktionen kommt in Spezialfällen zur Anwendung (Schreiben von Logfiles, soweit ich mich erinnere - aber das kann man ignorieren, wenn man die nicht auswertet).

dedlfix.

[Rolf B]

Hallo tk,

Das wird so nicht funktionieren: die Werte in $_GET (und auch $_POST) sind immer Strings, egal welcher Wert darin steht.

Nein. Der Webserver initialisiert sie als Strings. Danach sind sie Freiwild.

$_GET['id'] = intval($_GET['id']);
echo gettype($_GET['id']);

liefert integer. Plus Gemaule von PHP, wenn man den id-Parameter nicht übergibt, ich weiß.

Aber ich hätte auch eigentlich gar nich im Sinn gehabt, einen $_GET-Eintrag zu überschreiben; ich hätte dafür eine neue Variable gemacht. Einfach zur Sicherheit, um nicht irgendwo im Code auf $_GET[...] zuzugreifen und überlegen zu müssen: Ist dieser Wert jetzt validiert, oder nicht?

ctype_digit()

Kann man ergänzen, ja. intval("12a") liefert 12. Aber intval sollte man auf jeden Fall verwenden, nicht filter_input mit FILTER_VALIDATE_INT, der flippt bei "010" aus und kriegt die Oktalpest.

global ist böse™

Naja, gelegentlich erlaube ich mir da auch die eine oder andere Bosheit, oder tarne die global-Variable als Instanzvariable einer Worker-Klasse oder als static property einer anderen Klasse.

Man muss auch immer sehen, dass Linuchs kein Jungspund mehr ist, der gerade das Programmieren anfängt, sondern seit Jahrzehnten aktiv ist. Meine frühere Chefin meinte mal: Einen guten COBOL Programmierer erkennt man daran, dass er in jeder beliebigen Programmiersprache COBOL-Programme schreiben kann.

Ich weiß zwar nicht, ob Linuchs eine unglückliche Jugend als COBOL-Knecht hatte, aber wenn man mit globals groß geworden ist, fällt eine abweichende Denke schwer. Das nehme ich dann einfach hin. Es funktioniert für ihn, es ist sein Stil, und dagegen anzudiskutieren ist unnötiger Energieaufwand für alle Beteiligten. Ich mache gelegentlich Anmerkungen dazu, und wenn er sie annimmt, freue ich mich. Wenn nicht - na gut. Vielleicht lernt ein anderer was draus.

Rolf