kostenloser Webspace werbefrei: lima-city


Stored Functions // hierarchische Abfrage

lima-cityForumProgrammiersprachenPHP, MySQL & .htaccess

  1. Autor dieses Themas

    wagnerm

    wagnerm hat kostenlosen Webspace.

    Hi Leute,

    ich möchte eine hierarchische Abfrage direkt in der Datenbank erledigen und dazu brauche ich eine STORED FUNCTION, die mir das ermöglicht. Hier sind die Quellen für meine Functions: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ und http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/.

    Wie ich aber in diesem Thread gelesen habe, ist es offensichtlich hier auf Lima-City nicht möglich, Stored Functions zu nützen. Gibt es irgend eine Alternative zu Stored Functions in MySQL? Ist die Tabelle mysql.func global oder je User-DB vorhanden, so dass das Anlegen und Löschen von Stored Functions über Berechtigungen gesteuert werden kann?

    Wäre über eure Antwort und vor allem über eine Lösung sehr dankbar!
  2. Diskutiere mit und stelle Fragen: Jetzt kostenlos anmelden!

    lima-city: Gratis werbefreier Webspace für deine eigene Homepage

  3. Auf deiner verlinkte Seite steht doch
    [In MySQL there is no such construct, but it can be emulated./b]

    Weiterhin steht hier http://www.lima-city.de/thread/mysql-funktion klipp und klar "is nicht".

    Und noch weiter, wofür brauchst du es? Das hört sich für mich eher so an, als ob du versucht ein Problem mit einem Konzept zu lösen, was du mal irgendwo aufgegriffen hast.
    Lass mal hören, was du vorhast, dann kann man dir sicherlich besser helfen.

    Gruß Lucas
  4. Autor dieses Themas

    wagnerm

    wagnerm hat kostenlosen Webspace.

    lucas9991 schrieb:
    Auf deiner verlinkte Seite steht doch
    [In MySQL there is no such construct, but it can be emulated./b]

    Ja, und genau diese Emulation sollte eben mit der Stored Procedure erfolgen.
    lucas9991 schrieb:
    Weiterhin steht hier http://www.lima-city.de/thread/mysql-funktion klipp und klar "is nicht".
    OK, deshalb auch meine Frage, ob es eine Alternative gibt.
    lucas9991 schrieb:
    Und noch weiter, wofür brauchst du es? Das hört sich für mich eher so an, als ob du versucht ein Problem mit einem Konzept zu lösen, was du mal irgendwo aufgegriffen hast.
    Lass mal hören, was du vorhast, dann kann man dir sicherlich besser helfen.
    Ich möchte eine hierarchische Abfrage erstellen, die mir meine Navigation ausgibt. Die Navigation habe ich in einer Tabelle als Baumstruktur mit NAV_ID als Primärschlüssel und PARENT_ID als Fremdschlüssel zum Elternknoten gespeichert. Siehe folgendes Schema:
    +--------+-----------+
    | nav_id | parent_id |
    +--------+-----------+
    |      1 |         0 |
    |      2 |         0 |
    |      3 |         1 |
    |      4 |         1 |
    |      5 |         2 |
    |      6 |         2 |
    |      7 |         5 |
    |      8 |         2 |
    |      9 |         3 |
    +--------+-----------+
    In MS SQL Server gibt es die Mögichkeit der Common Table Expression (CTE) in Kombination mit der WITH-Clause, um rekursive Abfragen zu gestalten (vgl. http://consultingblogs.emc.com/christianwade/archive/2006/09/20/SQL-Server-Standard-_2D00_-Recursive-Hierarchies-to-XML.aspx). Weiters gibt es hierzu in Oracle die START WITH ... CONNECT BY-Syntax, die ebenfalls rekursive Abfragen ermöglicht (vgl. http://sql-plsql-de.blogspot.com/2010/05/hierarchische-abfragen-in-oracle11g.html). Letztere sollte mit der Stored Function emuliert werden. Mittlerweile habe ich auch gesehen, dass Oracle ebenso die (modernere) Vorgehensweise mit der WITH-Clause wie der SQL Server unterstützt.

    Ich suche jetzt nach einer Möglichkeit, auch in MySQL so eine Abfrage zu erstellen bzw. diese Vorgehensweise zu simulieren.

    Danke schon mal für deine Hilfe. :biggrin:

    Beitrag zuletzt geändert: 11.4.2011 12:02:07 von wagnerm
  5. Vergiss es einfach. Stored Procedures gelten nicht ohne Grund als verpöhnt. Mache das selbe einfach mit einem normalen PHP-Script und cache das Ergebnis, wenn deine Seite denn so beliebt sein sollte. Ich gehe mal davon aus, dass sich deine Navigation nicht alle paar Millisekunden ändert, oder?
  6. Autor dieses Themas

    wagnerm

    wagnerm hat kostenlosen Webspace.

    Hallo Leute,

    ich hab jetzt doch noch eine Lösung ausfindig machen können, die auch ohne Stored Function oder Stored Procedure auskommt. In Anlehung an Yaslaw.Info konnte ich mir eine meinen Vorstellungen entsprechende SQL-Abfrage zusammenstellen. Die Lösung #3 von Yaslaw hat irgendwo einen Fehler und funktioniert nicht richtig. Aus diesem Grund habe ich aufbauend auf die funktionierende Lösung #2 meine Abfrage daraus entwickelt. Ein Nachteil an der Lösung von Yaslaw ist die Abhängigkeit zur ID eines Eintrages bei der Sortierung. Ein Navigationseintrag sollte aber auch nachträglich eingefügt und eingereiht werden können. Deshalb habe ich in die Tabelle 'nav' ein neues Attribut 'sort_crit' eingeführt, über das man die Sortierung der Navigationselemente steuern kann:
    +--------+-----------+-----------+
    | nav_id | parent_id | sort_crit |
    +--------+-----------+-----------+
    |      1 |         0 |         0 |
    |      2 |         1 |         0 |
    |      3 |         2 |         1 |
    |      4 |         1 |         1 |
    |      5 |         4 |         0 |
    |      6 |         5 |         0 |
    |      7 |         6 |         0 |
    |      8 |         2 |         0 |
    |      9 |        10 |         0 |
    |     10 |         8 |         0 |
    |     11 |         0 |         1 |
    |     12 |        11 |         0 |
    |     13 |        11 |         1 |
    +--------+-----------+-----------+
    Des Weiteren verwende ich statt NULL als Wurzel den Wert 0 (siehe dazu Wikipedia).

    Nachstehend darf ich euch meine weiterentwickelte Lösung vorstellen, die zusätzlich den Pfad ausgehend vom Wurzelknoten zum entsprechenden Knoten und das Level in der Hierarchie ausgibt:
    SELECT
        CAST(id AS CHAR) AS nav_id,
        CAST(GROUP_CONCAT(pathid ORDER BY rownum DESC SEPARATOR '-') AS CHAR) AS path,
        COUNT(pathid) - 1 AS level,
        CAST(GROUP_CONCAT(pathsortcrit ORDER BY rownum DESC SEPARATOR '-') AS CHAR) AS sortcrit
    FROM
        (
            SELECT
                # Zeilennummer. Wird später für die Sortierung des GROUP_CONCAT verwendet
                @rownum := @rownum+1 AS rownum,
                # id die für den Pfad verwendet wird
                IF(@lastid <> mylist.nav_id, @id := mylist.nav_id, @id) AS pathid,
                LPAD(IF(@lastid <> mylist.nav_id, @sortcrit := mylist.sort_crit, @sortcrit), 3, 0) AS pathsortcrit,
                # Die Start-Id.
                @lastid := mylist.nav_id AS id,
                # bestimmen der nächsten id im Path
                @id := (SELECT parent_id FROM nav WHERE nav_id = @id) AS parent_id,
                # Sortierkriterium
                @sortcrit := (SELECT sort_crit FROM nav WHERE nav_id = @id) AS sort_crit
            FROM
                # Variablen initialisieren
                (SELECT @id := 0, @lastid := 0, @sortcrit := 0, @rownum := 0) AS vars,
                # Die Tabelle mit sich selber multiplizieren um genügend
                # Zeilen zur Verfügung zu haben
                (SELECT nav_id FROM nav WHERE parent_id <> 0) AS myloop,
                (SELECT nav_id, sort_crit FROM nav) AS mylist
        ) AS t
    WHERE
        pathid IS NOT NULL
     GROUP BY
        id
     ORDER BY
        sortcrit ASC;
    Das Resultat sieht folgendermaßen aus:
    +--------+--------------+-------+---------------------+
    | nav_id | path         | level | sortcrit            |
    +--------+--------------+-------+---------------------+
    | 1      | 0-1          |     1 | 000                 |
    | 2      | 0-1-2        |     2 | 000-000             |
    | 8      | 0-1-2-8      |     3 | 000-000-000         |
    | 10     | 0-1-2-8-10   |     4 | 000-000-000-000     |
    | 9      | 0-1-2-8-10-9 |     5 | 000-000-000-000-000 |
    | 3      | 0-1-2-3      |     3 | 000-000-001         |
    | 4      | 0-1-4        |     2 | 000-001             |
    | 5      | 0-1-4-5      |     3 | 000-001-000         |
    | 6      | 0-1-4-5-6    |     4 | 000-001-000-000     |
    | 7      | 0-1-4-5-6-7  |     5 | 000-001-000-000-000 |
    | 11     | 0-11         |     1 | 001                 |
    | 12     | 0-11-12      |     2 | 001-000             |
    | 13     | 0-11-13      |     2 | 001-001             |
    +--------+--------------+-------+---------------------+
    13 rows in set (0.02 sec)
    Ich danke euch trotzdem für eure Hilfe und hoffe, dass diese Lösung auch anderen Mitgliedern der Community hilfreich und nützlich ist. Ich werde diese Lösung aber trotzdem noch auf Effizienz überprüfen und ggf. optimieren.

    Beitrag zuletzt geändert: 13.4.2011 10:33:00 von wagnerm
  7. Ich habe jetzt nicht alles durchgelesen, aber das klingt für mich nach einer Einsatzmöglichkeit für Nested Sets.
  8. Autor dieses Themas

    wagnerm

    wagnerm hat kostenlosen Webspace.

    deddyh schrieb:
    Ich habe jetzt nicht alles durchgelesen, aber das klingt für mich nach einer Einsatzmöglichkeit für Nested Sets.
    Danke für den Hinweis! Das ist in der Tat auch eine weitere Möglichkeit, hierarchische Daten zu verwalten.

    Ich wollte aber keine zusätzlichen Spalten für 'Left' und 'Right' einführen. Außerdem erscheint mir die Verwaltung dieser beiden Spalten nicht sehr trivial. Bei der oben genannten Lösung muss man sich darum überhaupt nicht kümmern. Es können einfach Knoten gelöscht, eingefügt und das Sortierkriterium beliebig angegeben werden, ohne dass andere Knoten manipuliert werden müssen. :wink:

    Beitrag zuletzt geändert: 13.4.2011 10:24:19 von wagnerm
  9. Diskutiere mit und stelle Fragen: Jetzt kostenlos anmelden!

    lima-city: Gratis werbefreier Webspace für deine eigene Homepage

Dir gefällt dieses Thema?

Über lima-city

Login zum Webhosting ohne Werbung!