SQL: IF funktioniert nicht

[jo]

Hallo.

Folgende Situation:
Nested Set für Kategorien.
Es gibt folgende Einträge:

categoryid categoryname lft rgt level
  1 Oberkategorien 1 22 1
  2 Kommunikation 2 17 2
  3 Auto & Verkehr 18 21 2
  4 Beauty   22 23 2
  5 Ktheorien 3 12 3
  6 Kwissenschaften 13 14 3
  7 Paul Watzlawick 4 5 4
  8 Schulz von Thun 6 7 4
  9 Niklas Luhmann 8 9 4
  10 David Bohm 10 11 4
  11 Führerschein 19 20 3

Der folgende Query:

SELECT  
IF(node2.lft+1!=node2.rgt, node1.categoryname, node2.categoryid) AS res  
FROM board_category AS node1  
LEFT JOIN board_category AS node2 ON node2.categoryname='Ktheorien'  
WHERE  
node1.lft BETWEEN node2.lft AND node2.rgt  
AND node1.level = node2.level+1

soll folgendes tun:
Er soll mir alle Kinder von Ktheorien holen aber nicht die Kindes-Kinder.
Ergebnis sollte also sein: Paul Watzlawick, Schulz, von Thun, Niklas Luhmann und David Bohm.
Das klappt auch.
Nun - folgender Query ist genau wie der obrige nur mit "Paul Watzlawick" als Kategoriename:

SELECT  
IF(node2.lft+1!=node2.rgt, node1.categoryname, node2.categoryid) AS res  
FROM board_category AS node1  
LEFT JOIN board_category AS node2 ON node2.categoryname='Paul Watzlawick'  
WHERE  
node1.lft BETWEEN node2.lft AND node2.rgt  
AND node1.level = node2.level+1

Paul Watzlawick hat keine Kinder. Was soll er also tun? Stattdessen soll er mir doch wenigstens Paul Watzlawicks ID zurückgeben - damit ich im weiteren Programmfluss alternativ mit der Situation umgehen kann.
Das klappt leider nicht und ich weiß nicht warum.
Wenn Paul keine Kinder hat muss sein lft-Wert + 1 = sein rgt-Wert sein. Logisch.
Wenn also dieser Fall sein sollte und Paul keiner Kinder hat gibt IF-mir die KategorieID zurück - ich bin ja vorher genau an dem Punkt gejoint wo der Name "Paul Watzlawick" lautet.

Warum klappt das nicht? Ich bekomme ein leeres Array zurück.

Grüße,

Jo

[ChrisB]

Hi,

du hast doch bereits einen Thread offen, in dem du den Umgang mit Nested Sets in SQL diskutierst.
Da passt diese Frage thematisch auch noch gut mit rein - also bringe sie bitte auch dort unter, und unterlasse Doppelpostings.

MfG ChrisB

[jo]

Hi Chris.

Damit hast du ja eigentl. Recht.

Die Frage ist allerdings eine ganz andere - dazu kommt das das Problem nichts mit Nested Sets zu tun hat sondern nur in einer sollchen Umgebung vorhanden ist.

Deshalb sah ich es für angebrachter an einen neuen Thread zu eröffnen.
Die ausführliche Beschreibung meines Nested Sets "Systems" im Post habe ich trotzdem gemacht um den Query besser zu verstehen.

Grüße, Jo

[Struppi]

Deshalb sah ich es für angebrachter an einen neuen Thread zu eröffnen.

Dir muss aber klar sein, dass du damit es potentiellen Helfern schwerer machst. Da sich durch den anderen Thread die zusammenhänge besser erkennen liesse.
Ein neuer Thread ruft nicht mehr Aufmerksamkeit nach sich, falls das deine Intention war. Da sich registrierte Benutzer ihre Threads vermutlich markieren und nach ihren Wünschen sortieren.

Struppi.

[dedlfix]

Hi!

[Eine Abfrage] soll folgendes tun:
Er soll mir alle Kinder von Ktheorien holen aber nicht die Kindes-Kinder.
Das klappt auch.
Nun - folgender Query ist genau wie der obrige nur mit "Paul Watzlawick" als Kategoriename:
Paul Watzlawick hat keine Kinder.

Wenn er keine Kinder hat, kann es keine Kinder als Ergebnis geben. Du willst nun stattdessen "das Elter", aber das hast du ja gar nicht angefragt, auch nicht bei der ersten Abfrage, sondern nur dessen Kinder. Wo soll es also plötzlich herkommen?

Was soll er also tun? Stattdessen soll er mir doch wenigstens Paul Watzlawicks ID zurückgeben - damit ich im weiteren Programmfluss alternativ mit der Situation umgehen kann.

Es sieht mir so aus, als ob die erste Abfrage eine Ergebnismenge liefert, über die du nun iterierst und die zweite darauf anwendest. Dann hast du doch bereits alle Angaben und kannst bei leerer Ergebnismenge im abfragenden Programm so reagieren, dass du die Daten aus dem Datensatz der ersten Abfrage nimmst.

Warum klappt das nicht? Ich bekomme ein leeres Array zurück.

Leeres Array? Die Ergebnismenge einer SQL-Abfrage ist doch kein Array.

Lo!

[Jo]

Wenn er keine Kinder hat, kann es keine Kinder als Ergebnis geben. Du willst nun stattdessen "das Elter", aber das hast du ja gar nicht angefragt, auch nicht bei der ersten Abfrage, sondern nur dessen Kinder. Wo soll es also plötzlich herkommen?

Durch den JOIN kann ich doch jederzeit auf die Elemente zurückgreifen oder?

Es sieht mir so aus, als ob die erste Abfrage eine Ergebnismenge liefert, über die du nun iterierst und die zweite darauf anwendest. Dann hast du doch bereits alle Angaben und kannst bei leerer Ergebnismenge im abfragenden Programm so reagieren, dass du die Daten aus dem Datensatz der ersten Abfrage nimmst.

Ich brauche dann aber die Kategorie-ID des Elternelements welches ich ja durch den JOIN bekommen müsste.

Warum klappt das nicht? Ich bekomme ein leeres Array zurück.

Leeres Array? Die Ergebnismenge einer SQL-Abfrage ist doch kein Array.

Meine Datenbank-Klasse sorgt dafür das ich alles in Arrays zurückbekomme.

Kein Ergebnis - leeres Array.

Lo!

[dedlfix]

Hi!

Wenn er keine Kinder hat, kann es keine Kinder als Ergebnis geben. Du willst nun stattdessen "das Elter", aber das hast du ja gar nicht angefragt, auch nicht bei der ersten Abfrage, sondern nur dessen Kinder. Wo soll es also plötzlich herkommen?
Durch den JOIN kann ich doch jederzeit auf die Elemente zurückgreifen oder?

FROM und JOIN liefern dir alles, aber das WHERE schließt einiges aus. Wenn du Elter und Kinder haben möchtest, musst du das im WHERE berücksichtigen, dann bekommst du einen Datensatz mehr geliefert. Solange WHERE keine Datensätze für die Ergebnismenge liefert, kannst du auch keine Daten hinzufügen - sprich: wirst du die ID nicht bekommen.

Nochmal die Frage: Warum willst du da die ID haben? Die hast du doch eigentlich schon von der vorherigen Abfrage. Eine leere Ergebnismenge kann genauso gut als Indikator verwendet werden, wie du es eigentlich mit der ID vorhast.

Meine Datenbank-Klasse sorgt dafür das ich alles in Arrays zurückbekomme.

Ahja, ist aber für dein Problem nicht relevant und sorgt bestenfalls nur für Verwirrung.

Lo!

[jo]

Hi.

FROM und JOIN liefern dir alles, aber das WHERE schließt einiges aus. Wenn du Elter und Kinder haben möchtest, musst du das im WHERE berücksichtigen, dann bekommst du einen Datensatz mehr geliefert. Solange WHERE keine Datensätze für die Ergebnismenge liefert, kannst du auch keine Daten hinzufügen - sprich: wirst du die ID nicht bekommen.

Wie mache ich das denn in diesem Fall?

Nochmal die Frage: Warum willst du da die ID haben? Die hast du doch eigentlich schon von der vorherigen Abfrage. Eine leere Ergebnismenge kann genauso gut als Indikator verwendet werden, wie du es eigentlich mit der ID vorhast.

Ich brauche nicht den Indikator nach dem Motto erfolgreich/nicht erfolgreich sondern wirklich die ID da später überprüft wird - ID oder Name der Kategorie und dementsprechend etwas anderes ausgegeben wird. Sagen wir so es erspart mir weitere Queries und einige Zeilen Quelltext sowie Rechenzeit.

Meine Datenbank-Klasse sorgt dafür das ich alles in Arrays zurückbekomme.

Ahja, ist aber für dein Problem nicht relevant und sorgt bestenfalls nur für Verwirrung.

Du hast Recht - entschuldige.
Grüße, Jo

[dedlfix]

Hi!

FROM und JOIN liefern dir alles, aber das WHERE schließt einiges aus. Wenn du Elter und Kinder haben möchtest, musst du das im WHERE berücksichtigen, dann bekommst du einen Datensatz mehr geliefert. Solange WHERE keine Datensätze für die Ergebnismenge liefert, kannst du auch keine Daten hinzufügen - sprich: wirst du die ID nicht bekommen.

Wie mache ich das denn in diesem Fall?

Deine WHERE-Klausel sieht momentan so aus, dass du von KategorieX die Kinder des nächsten Levels abfragst. Von denen willst du den categoryname haben. KategorieX selbst ist nicht in der Abfrage berücksichtigt, ist kein Teil des Ergebnisses. So soll es eigentlich sein. Nun willst du, wenn KategorieX keine Kinder hat, die Abfrage keine Ergebnisse liefert, plötzlich doch die KategorieX enthalten haben. Das widerspricht sich. Von nichts kann man nichts selektieren. Du kannst entweder nur KategorieX ständig mit in das Ergebnis aufnehmen und hast im Kinderfall das Problem sie von den Kindern unterscheiden zu müssen, oder du suchst dir einen anderen Weg der Erkennung.

Die Ergebnismenge ständig um einen Elter-Datensatz zu erweitern sollte nicht das Problem sein. Die WHERE-Klausel kann dazu um OR node1.level = node2.level erweitert werden. Das IF() kann anhand des Levels zwischen Elter und Kindern unterscheiden. In der Auswertelogik musst du nun zusätzlich berücksichtigen: Wenn mehr als ein Datensatz in der Ergebnismenge ist, dann muss der Elterdatensatz ignoriert werden.

Bereits im DBMS das Elter fallabhängig einzubauen geht nicht mit einer simplen Abfrage. Da müsstest du mit einer Stored Procedure zu Werke gehen oder ein recht komplexes JOIN hinzufügen. Das darf sich nur hinzufügen, wenn keine Kinder in der Ergebnismenge sind, wozu es die erst einmal für sich selbst aufbauen muss. Da siehst du am Ende keiner mehr durch.

Lo!

[jo]

hi.

Kurzuzusammenfassung, ich komme nicht um eine Stored Produce oder um einen zweiten Query rum - sehe ich das richtig?

Lieben Gruß,

Jo

[dedlfix]

Hi!

Kurzuzusammenfassung, ich komme nicht um eine Stored Produce oder um einen zweiten Query rum - sehe ich das richtig?

Oh doch, es kommt darauf an, für welchen der verschiedenen Lösungsansätze du dich entscheiden willst oder das bereits getan hast. Also nochmal.

Variante 1:

Die Abfrage bleibt wie sie jetzt ist, das IF() kann aber zugunsten des categoryname raus. Die abfragende Logik (die PHP-Funktion, die das SQL-Statement absendet und die Ergebnismenge entgegennimmt) stellt fest, wenn die Ergebnismenge leer ist (keine Kinder), soll sie die ID des Vorfahren zurückliefern. Die müsste sie eigentlich schon haben, denn zu genau diesem Vorfahren soll sie ja eigentlich die Kinder liefern.

Geringer Aufwand, kleine Änderung der Abfragelogik

Variante 2:

Das Statement wird um die Abfrage des Vorfahren erweitert. Für diesen gibt es per IF() die ID oder für die Kinder den categoryname. Im Kinderfall muss die Abfragelogik den Vorfahrdatensatz entfernen. Anderenfalls kommt nur der Vorfahrdatensatz (und darin die ID).

Ungefähr gleicher Aufwand wie bei Variante 1, aber etwas umständlicher und undurchsichtger.

Variante 3:

Die Kinderabfrage erfolgt wie bisher. Die Abfragelogik schickt jedoch eine Vorfahrabfrage hinterher, wenn es keine Kinder gibt.

Deutlich aufwendiger als V1 und V2, aber noch erträglich.

Variante 4:

Eine Stored Procedure ist noch aufwendiger, weil du vermutlich erst einmal die dazu notwendigen SQL-Anweisungen lernen musst. Zudem könnte sich ein Umstieg auf mysqli notwendig machen, wenn du bisher die mysql-Extension verwendet hast.

Lo!

[Jo]

Hi!

Variante 1:

Die Abfrage bleibt wie sie jetzt ist, das IF() kann aber zugunsten des categoryname raus. Die abfragende Logik (die PHP-Funktion, die das SQL-Statement absendet und die Ergebnismenge entgegennimmt) stellt fest, wenn die Ergebnismenge leer ist (keine Kinder), soll sie die ID des Vorfahren zurückliefern. Die müsste sie eigentlich schon haben, denn zu genau diesem Vorfahren soll sie ja eigentlich die Kinder liefern.

Geringer Aufwand, kleine Änderung der Abfragelogik

Klingt ja als am sinnvollsten.
Nur heißt das - zwei Queries oder?

Ich schicke also den hier ab:

SELECT  
IF(node2.lft+1!=node2.rgt, node1.categoryname, node2.categoryid) AS res  
FROM board_category AS node1  
LEFT JOIN board_category AS node2 ON node2.categoryname='Paul Watzlawick'  
WHERE  
node1.lft BETWEEN node2.lft AND node2.rgt  
AND node1.level = node2.level+1

Und falls das Ergebnis leer ist, schicke ich diesen hinterher:

  
SELECT categoryid FROM board_category WHERE categoryname='Paul Watzlawick'

Oder verstehe ich das falsch?

Eine Stored Procedure ist noch aufwendiger, weil du vermutlich erst einmal die dazu notwendigen SQL-Anweisungen lernen musst. Zudem könnte sich ein Umstieg auf mysqli notwendig machen, wenn du bisher die mysql-Extension verwendet hast.

Richtig die müsste ich noch lernen. Ich nutze bereits MySQLi.
Was wäre denn performanter? Auf die Geschwindigkeit am Ende kommt es mir an. Was ich dafür lernen muss - sei mal dahergestellt.

Gruß, Jo

[dedlfix]

Hi!

Variante 1:

Die Abfrage bleibt wie sie jetzt ist, das IF() kann aber zugunsten des categoryname raus. Die abfragende Logik (die PHP-Funktion, die das SQL-Statement absendet und die Ergebnismenge entgegennimmt) stellt fest, wenn die Ergebnismenge leer ist (keine Kinder), soll sie die ID des Vorfahren zurückliefern. Die müsste sie eigentlich schon haben, denn zu genau diesem Vorfahren soll sie ja eigentlich die Kinder liefern.

Geringer Aufwand, kleine Änderung der Abfragelogik

Klingt ja als am sinnvollsten.
Nur heißt das - zwei Queries oder?

Nein, das wäre Variante 3 gewesen.

Ich schicke also den hier ab:

SELECT

IF(node2.lft+1!=node2.rgt, node1.categoryname, node2.categoryid) AS res
FROM board_category AS node1
LEFT JOIN board_category AS node2 ON node2.categoryname='Paul Watzlawick'
WHERE
node1.lft BETWEEN node2.lft AND node2.rgt
AND node1.level = node2.level+1

  
Das IF() kann - wie bereits gesagt - raus. Es trifft nie zu, weil das Vorfahr-Element auf dass es passen könnte, kein Ergebnismengenbestandteil wird. Dazu muss man wissen, in welcher Reihenfolge die SQL-Klauseln ausgewertet werden (jeweils, wenn vorhanden):  
  
- FROM und JOIN holen die sich die zu verarbeitenden Tabellen. Die JOIN-Bedingung filtert bereits erste Datensätze raus.  
- WHERE filtert anschließend weitere Datensätze weg.  
- GROUP BY ist nun an der Reihe und gruppiert die Ergebnismenge.  
- Nun erst kommt SELECT und berechnet die Spalten der Ergebnismenge.  
- HAVING filtert aus diesem Ergebnis weitere Reihen weg.  
- ORDER BY sortiert den Rest.  
- LIMIT kürzt zu guter Letzt auch noch weg.  
  
Diese Reihenfolge dient der groben Orientierung. Tatsächlich kann es durch den Optimierer des DBMS zu einer anderen Reihenfolge kommen, aber das ändert am Ergebnis nichts.  
  
Du solltest nun sehen, dass, wenn du mit WHERE keinen Vorfahr durchlässt, keine Berechnungen im SELECT damit angestellt werden können.  
  

> Und falls das Ergebnis leer ist, schicke ich diesen hinterher:  
> SELECT categoryid FROM board\_category WHERE categoryname='Paul Watzlawick'  
> Oder verstehe ich das falsch?  
  
Wie gesagt, das wäre Variante 3 gewesen. Nummer eins geht davon aus, dass dir die Kategorien-ID bereits bekannt ist. Woher hast du sonst den categoryname? Wenn du das bisher noch nicht machst, könntest du bei dessen Abfrage gleich die ID ermitteln, und sparst dir nun einen Aufwand.  
  

> Was wäre denn performanter? Auf die Geschwindigkeit am Ende kommt es mir an.  
  
Am performantesten sind die Dinge, die nicht durchgeführt werden müssen. Ob eine SP oder die Auswertung in PHP schneller ist, kann ich dir nicht sagen. Billiger als mit Variante 1 wirst du damit nicht kommen. Ich bin ja nach wie vor der Meinung, dass du die ID bereits vorliegen haben müsstest (den Namen bekommst du ja auch irgendwo her). Wenn das nicht der Fall ist, überleg doch noch mal, ob du sie nicht bereits in einem früheren, sowieso schon vorhandenen Schritt besorgen kannst. Das wäre aus meiner Sicht das Gescheiteste.  
  
  
Lo!

[jo]

Hi.

Das IF() kann - wie bereits gesagt - raus. Es trifft nie zu, weil das Vorfahr-Element auf dass es passen könnte, kein Ergebnismengenbestandteil wird.

Das sieht dann so aus:

SELECT  
node1.categoryname AS res,  
node2.categoryid  
FROM board_category AS node1  
LEFT JOIN board_category AS node2 ON node2.categoryname='Paul W.'  
WHERE  
node1.lft BETWEEN node2.lft AND node2.rgt  
AND node1.level = node2.level+1

Wie gesagt, das wäre Variante 3 gewesen. Nummer eins geht davon aus, dass dir die Kategorien-ID bereits bekannt ist. Woher hast du sonst den categoryname? Wenn du das bisher noch nicht machst, könntest du bei dessen Abfrage gleich die ID ermitteln, und sparst dir nun einen Aufwand.

Categoryname kommt aus der URL.
Die Abfrage ist leider die erste und die einzige Abfrage die die gebraucht wird. Ich habe also nur die Wahl - ein und zwei Queries.
Und ich würde da gerne bei einem bleiben.
Also gibts da keine möglichkeit den Query so zurecht zu biegen?

=/

Bye.

[dedlfix]

Hi!

Categoryname kommt aus der URL.
Die Abfrage ist leider die erste und die einzige Abfrage die die gebraucht wird. Ich habe also nur die Wahl - ein und zwei Queries.
Und ich würde da gerne bei einem bleiben.
Also gibts da keine möglichkeit den Query so zurecht zu biegen?

Dann wäre Variante 2 diejenige mit dem geringsten Aufwand. Du kannst dann die Abfrage wie bisher (mit IF) verwenden, müsstest aber die WHERE-Bedingung erweitern und per OR verknüpft den Vorfahrdatensatz einschließen.

Der PHP-Teil sieht sich dein Ergebnis-Array an. Wenn ein Datensatz enthalten ist, ist es der Vorfahr, ansonsten müsstest du den Vorfahrsatz suchen (Inhalt ist nummerisch (is_numeric())) und streichen (unset()), um nur die Kinder zu behalten.

Lo!

[jo]

Danke für deine Mühe Dedlfix.
Ich habe es _ganz_ anders gelöst aber diesen Lösungsweg zu erklären wäre für nachfolgende Leser uninteressant da ich mein komplettes System erklären müsste ;)

Lieben Gruß, jo