Suche Hilfe bei MySQL-Abfrage

[Werner]

Hallo!

Ich verzweifle an einer Datenbankabfrage für eine Benutzerverwaltung und hoffe nun,
dass mir hier jemand helfen kann.

Ich habe 3 Tabellen "groups", "user", "correlation_groups_user".
Die Gruppen sind als nested set abgelegt.

┌──────────────────────────────┐ ┌───────────────┐ ┌──────────────────┐
│         table.groups         │ │   table.user  │ │ table.corre_g_u  │
├─────────┬──────┬──────┬──────┤ ├────────┬──────┤ ├─────────┬────────┤
│ groupId │ name │ lKey │ rKey │ │ userId │ name │ │ groupId │ userId │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 1       │ foo  │ 1    │ 10   │ │ 1      │ Uwe  │ │ 1       │ 1      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 2       │ bar  │ 2    │ 5    │ │ 2      │ Klaus│ │ 1       │ 2      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 3       │ bla  │ 3    │ 4    │ │ 3      │ Gabi │ │ 2       │ 3      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 4       │ blub │ 6    │ 7    │ │ 4      │ Inge │ │ 3       │ 1      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 5       │ beta │ 8    │ 9    │ │ 5      │ Gerd │ │ 3       │ 4      │
└─────────┴──────┴──────┴──────┘ ├────────┼──────┤ ├─────────┼────────┤
                                 │ 6      │ Sepp │ │ 4       │ 5      │
                                 └────────┴──────┘ ├─────────┼────────┤
                                                   │ 5       │ 6      │
                                                   └─────────┴────────┘

Die Benutzerverwaltung ist ein Modul das in einer weiteren Tabelle
"correlation_moduls_groups" mit der "groupId" verknüpft wird.
(modulId 1 = Benutzerverwaltung)

┌───────────────────┐
│ table.corre_m_g   │
├─────────┬─────────┤
│ modulId │ groupId │
├─────────┼─────────┤
│ 1       │ 1       │
├─────────┼─────────┤
│ 1       │ 2       │
├─────────┼─────────┤
│ 1       │ 4       │
└─────────┴─────────┘

Was ich jetzt suche ist eine Abfrage die folgendes macht zurück gibt.
Alle Gruppen in denen ein Benutzer ist UND die Zugriff auf die Benutzerverwaltung haben.
Das ist soweit erstmal kein Problem.

Dann suche ich von den Gruppen alle Benutzer die in Untergruppen sind ABER NICHT in
übergeordneten Gruppen. Und daran scheitere ich.

Beispiele:

• Uwe sieht alle Benutzer ausser Klaus und sich selbst
• Klaus sieht alle Benutzer ausser Uwe und sich selbst
• Gabi sieht nur Inge
• Inge sieht gar nichts weil ihre Gruppe keinen Zugriff auf das Modul hat
• Gerd sieht niemanden weil seine Gruppe keine Untergruppe hat
• Sepp sieht aus dem selben Grund auch niemanden

Im Idealfall hätte ich gern nur eine Abfrage und keine doppelten Datensätze für den zweiten
Teil damit ich SQL_CALC_FOUND_ROWS noch verwenden kann. Das Ergebnis der Abfrage sollte
auch nach den Gruppennamen sortierbar sein.

Ich bin für jede Hilfe dankbar!

Beste Grüße!

[Siri]

Hallo,

Bedingung_1:

Alle Gruppen in denen ein Benutzer ist UND die Zugriff auf die Benutzerverwaltung haben.
Das ist soweit erstmal kein Problem.

Bedingung_2:

Dann suche ich von den Gruppen alle Benutzer die in Untergruppen sind ABER NICHT in
übergeordneten Gruppen. Und daran scheitere ich.

Ich bin mir nicht ganz sicher, ob ich alles verstanden hab, aber es schaut mir schematisch nach:

SELECT Bedingung_1 FROM (SELECT Bedingung_2 FROM ...)

aus. Allerdings kann ich in dem Beispiel nicht erkennen, wie übergeordnete Gruppen gekennzeichnet sind. Aber villeicht hilft dir der Ansatz weiter.

Gruß,
Siri

[Werner]

Allerdings kann ich in dem Beispiel nicht erkennen, wie übergeordnete Gruppen gekennzeichnet sind. Aber villeicht hilft dir der Ansatz weiter.

Danke erstmal für deine Hilfe! Ich werde mir den Ansatz mal durch den Kopf gehen lassen. Hauptsächlich weiß ich nicht wie ich die Benutzer von Untergruppen finde die aber nicht in übergeordneten oder der eigenen Gruppe sind.
Die Gruppen sind, wie gesagt, als nested set gespeichert.
Das heißt, "foo" ist über allen Gruppen, eine Ebene Tiefer sind "bar", "blub" und "beta", "bla" ist eine Untergruppe von "bar".

[Siri]

Danke erstmal für deine Hilfe! Ich werde mir den Ansatz mal durch den Kopf gehen lassen. Hauptsächlich weiß ich nicht wie ich die Benutzer von Untergruppen finde die aber nicht in übergeordneten oder der eigenen Gruppe sind.
Die Gruppen sind, wie gesagt, als nested set gespeichert.
Das heißt, "foo" ist über allen Gruppen, eine Ebene Tiefer sind "bar", "blub" und "beta", "bla" ist eine Untergruppe von "bar".

Vielleicht ist es ja noch etwas früh am Morgen... ;-) Aber ist nicht jeder irgendwie in einer Grúppe?

Ebene 1: foo (gid1) -> Klaus, Uwe
Ebene 2: bar (gid2) -> Gabi, blub(gid4)-> Gerd UND beta(gid5) -> Sepp
Ebene 2: bla (gid3) -> Uwe, Inge

[Werner]

Vielleicht ist es ja noch etwas früh am Morgen... ;-) Aber ist nicht jeder irgendwie in einer Grúppe?

Ebene 1: foo (gid1) -> Klaus, Uwe
Ebene 2: bar (gid2) -> Gabi, blub(gid4)-> Gerd UND beta(gid5) -> Sepp
Ebene 2: bla (gid3) -> Uwe, Inge

"bla" ist Ebene 3, aber ja genau so.
Und ja, jeder Benutzer ist in "irgeneiner" Gruppe.
Was bedeuten die (gid1), (gid2),...?

[Werner]

Was bedeuten die (gid1), (gid2),...?

Achso, die Gruppen-Ids. Ok.

[Siri]

Ebene 1: foo (gid1) -> Klaus, Uwe
Ebene 2: bar (gid2) -> Gabi, blub(gid4)-> Gerd UND beta(gid5) -> Sepp
Ebene 3: bla (gid3) -> Uwe, Inge

Ok!

Dann die raus, die nicht in table.corre_m_g sind (gid5 und gid3)

Ebene 1: foo (gid1) -> Klaus, Uwe
Ebene 2: bar (gid2) -> Gabi, blub(gid4)-> Gerd
Ebene 3:

Das wäre Bedingung_1, richtig?

Und was soll jetzt von der Restmenge genau übrig bleiben bzw. wie sähe deine Wunschergebnistabelle aus?

[Werner]

Dann die raus, die nicht in table.corre_m_g sind (gid5 und gid3)

Ebene 1: foo (gid1) -> Klaus, Uwe
Ebene 2: bar (gid2) -> Gabi, blub(gid4)-> Gerd
Ebene 3:

Das wäre Bedingung_1, richtig?

Das ist ja abhängig davon von welchem Benutzer man ausgeht.
Die erste Bedingung ist ja "Alle Gruppen in denen ein Benutzer ist UND die Zugriff auf die Benutzerverwaltung haben."
Richtiger wäre wohl "in denen ein bestimmter Benutzer ist". Also nicht alle Gruppen in denen mehr als 0 Benutzer sind.

Vielleicht haben wir da aneinander vorbei geredet.

Aber wie gesagt, ist das ja erstmal kein Problem und könnte in einer separaten Abfrage erfolgen. Ich dachte daher auch zunächst daran, die gefundenen Gruppen in einer WHERE-Klausel für die zweite Abfrage zu verwenden.

Und was soll jetzt von der Restmenge genau übrig bleiben bzw. wie sähe deine Wunschergebnistabelle aus?

Wie im ersten post beschrieben. Ausgehend von einem bestimmten Benutzer bestimmte andere Benutzer.

• Uwe sieht alle Benutzer ausser Klaus und sich selbst
• Klaus sieht alle Benutzer ausser Uwe und sich selbst
• Gabi sieht nur Inge
• Inge sieht gar nichts weil ihre Gruppe keinen Zugriff auf das Modul hat
• Gerd sieht niemanden weil seine Gruppe keine Untergruppe hat
• Sepp sieht aus dem selben Grund auch niemanden

[Vinzenz Mai]

Hallo Werner,

Die Gruppen sind als nested set abgelegt.

┌──────────────────────────────┐ ┌───────────────┐ ┌──────────────────┐
│         table.groups         │ │   table.user  │ │ table.corre_g_u  │
├─────────┬──────┬──────┬──────┤ ├────────┬──────┤ ├─────────┬────────┤
│ groupId │ name │ lKey │ rKey │ │ userId │ name │ │ groupId │ userId │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 1       │ foo  │ 1    │ 10   │ │ 1      │ Uwe  │ │ 1       │ 1      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 2       │ bar  │ 2    │ 5    │ │ 2      │ Klaus│ │ 1       │ 2      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 3       │ bla  │ 3    │ 4    │ │ 3      │ Gabi │ │ 2       │ 3      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 4       │ blub │ 6    │ 7    │ │ 4      │ Inge │ │ 3       │ 1      │
├─────────┼──────┼──────┼──────┤ ├────────┼──────┤ ├─────────┼────────┤
│ 5       │ beta │ 8    │ 9    │ │ 5      │ Gerd │ │ 3       │ 4      │
└─────────┴──────┴──────┴──────┘ ├────────┼──────┤ ├─────────┼────────┤
                                 │ 6      │ Sepp │ │ 4       │ 5      │
                                 └────────┴──────┘ ├─────────┼────────┤
                                                   │ 5       │ 6      │
                                                   └─────────┴────────┘

ehrlich gesagt verstehe ich nicht im geringsten, was diese Aussage

Dann suche ich von den Gruppen alle Benutzer die in Untergruppen sind ABER NICHT in
übergeordneten Gruppen. Und daran scheitere ich.

mit diesen Beispielen

• Uwe sieht alle Benutzer ausser Klaus und sich selbst
• Klaus sieht alle Benutzer ausser Uwe und sich selbst
• Gabi sieht nur Inge
• Inge sieht gar nichts weil ihre Gruppe keinen Zugriff auf das Modul hat
• Gerd sieht niemanden weil seine Gruppe keine Untergruppe hat
• Sepp sieht aus dem selben Grund auch niemanden

zu tun hat und

Im Idealfall hätte ich gern nur eine Abfrage und keine doppelten Datensätze für den zweiten
Teil damit ich SQL_CALC_FOUND_ROWS noch verwenden kann. Das Ergebnis der Abfrage sollte
auch nach den Gruppennamen sortierbar sein.

welches Ergebnis Du gerne hättest.

Aber ein paar Tipps:

1. Reine Untergruppen sind in Nested Sets dadurch gekennzeichnet,
   dass der Wert des rKey um genau 1 größer ist als der rKey:

-- Alle Blätter, d.h. reine Untergruppen

SELECT  
    groupId  
FROM  
    groups  
WHERE  
    rKey = lKey + 1;  

Ergebnis:

groupId
-------
   3
   4
   5

2. Somit ergibt sich für übergeordnete Gruppen, dass der rKey nicht
   um 1 größer sein darf als der rKey:

-- Alle übergeordneten Gruppen  
SELECT  
    groupId  
FROM  
    groups  
WHERE  
    rKey != lKey + 1;  

Ergebnis:

groupId
-------
   1
   2

3. Benutzer, die in Untergruppen sind (aber auch in übergeordneten
   Gruppen sein können), findest Du über

SELECT                   -- Gib mir  
    userId               -- die userIds  
FROM                     -- aus der Liste  
    groups_user          -- der Gruppen-User-Zuordnung  
WHERE                    -- deren  
    groupId              -- GruppenId  
IN (                     -- in der  
    SELECT               -- Liste der Blätter enthalten sind  
        groupId  
    FROM  
        groups  
    WHERE  
        rKey = lKey + 1  
);  

Ergebnis:

userId
-------
   1
   4
   5
   6

4. Analog findet man die Benutzer, die in übergeordneten Gruppen sind mit

SELECT  
    userId  
FROM  
    groups_user  
WHERE  
    groupId  
IN (  
	SELECT  
		groupId  
	FROM  
		groups  
	WHERE  
		rKey != lKey + 1  
);

Ergebnis

userId
-------
   1
   2
   3

und somit findet man Benutzer, die nur in Untergruppen sind, mit der Kombination von 3 und 4:

Gib mir alle Benutzer, die in 3 sind, aber nicht in 4 (es muss Benutzer 1 rausfallen):

SELECT                              -- Alle Benutzer  
    userId  
FROM  
    groups_user  
WHERE  
    groupId                         -- aus Untergruppen  
IN (  
    SELECT  
        groupId  
    FROM  
        groups  
    WHERE  
        rKey = lKey + 1  
)  
AND                                 -- die nicht  
    userId NOT IN (                 -- in Übergruppen sind  
        SELECT  
            userId  
        FROM  
            groups_user  
        WHERE  
            groupId  
        IN (  
            SELECT  
                groupId  
            FROM  
                groups  
            WHERE  
                rKey != lKey + 1  
        )  
    );  

Ergebnis:

userId
-------
   4
   5
   6

Freundliche Grüße

Vinzenz

[Werner]

ehrlich gesagt verstehe ich nicht im geringsten, was diese Aussage

Dann suche ich von den Gruppen alle Benutzer die in Untergruppen sind ABER NICHT in
übergeordneten Gruppen. Und daran scheitere ich.

mit diesen Beispielen

• Uwe sieht alle Benutzer ausser Klaus und sich selbst
• Klaus sieht alle Benutzer ausser Uwe und sich selbst
• Gabi sieht nur Inge
• Inge sieht gar nichts weil ihre Gruppe keinen Zugriff auf das Modul hat
• Gerd sieht niemanden weil seine Gruppe keine Untergruppe hat
• Sepp sieht aus dem selben Grund auch niemanden

zu tun hat und

Uwe und Klaus sind z.B. in der ersten Gruppe "foo". Uwe ist aber ausserdem in der Gruppe "bla". "bla" ist eine Untergruppe von "bar" in der Gabi Mitglied ist. Gabi soll aber Klaus nicht sehen weil er auch in einer übergeordneten Gruppe ist.
Gabi ist in meiner Aussage und dem Beispiel in der Gruppe von der ich alle Benutzer finden möchte die in Untergruppen aber nicht in übergeordneten Gruppen sind.

welches Ergebnis Du gerne hättest.

Dafür waren eigentlich die Beispiele.

Aber ein paar Tipps:

Herzlichen Dank für die ausführlichen Beispiele, ich werde mir das gleich genauer anschauen!

Beste Grüße!

[Werner]

Uwe und Klaus sind z.B. in der ersten Gruppe "foo". Uwe ist aber ausserdem in der Gruppe "bla". "bla" ist eine Untergruppe von "bar" in der Gabi Mitglied ist. Gabi soll aber _Uwe_ nicht sehen weil er auch in einer übergeordneten Gruppe ist.

fixed

[Vinzenz Mai]

Hallo Werner,

welches Ergebnis Du gerne hättest.

Dafür waren eigentlich die Beispiele.

das Ergebnis einer Abfrage hat eine tabellarische Form, besteht aus einer bestimmten Zahl Spalten und Ergebniszeilen. Diese tabellarische Form inklusive der gewünschten Inhalte aufgrund Deiner Ausgangsdaten benötige ich, um zu verstehen, was Du willst.

Freundliche Grüße

Vinzenz

[Werner]

das Ergebnis einer Abfrage hat eine tabellarische Form, besteht aus einer bestimmten Zahl Spalten und Ergebniszeilen. Diese tabellarische Form inklusive der gewünschten Inhalte aufgrund Deiner Ausgangsdaten benötige ich, um zu verstehen, was Du willst.

Sagte ich nicht, dass ich eine Liste mit Benutzern suche?
Wenn ich mir meine Beispieltabellen so anschaue, gibt es da nicht viel abzufragen. Es gibt nur Id und Name. Wenn ich also sage, dass Klaus alle Benutzer ausser Uwe und sich selbst sehen soll und die Tabelle nur Id und Namen her gibt... Es gibt nichts abzufragen ausser Id und Namen deshalb kann ich dein Problem einfach nicht nachvollziehen.

Beste Grüße!

[Sven Rautenberg]

Moin!

das Ergebnis einer Abfrage hat eine tabellarische Form, besteht aus einer bestimmten Zahl Spalten und Ergebniszeilen. Diese tabellarische Form inklusive der gewünschten Inhalte aufgrund Deiner Ausgangsdaten benötige ich, um zu verstehen, was Du willst.

Sagte ich nicht, dass ich eine Liste mit Benutzern suche?
Wenn ich mir meine Beispieltabellen so anschaue, gibt es da nicht viel abzufragen. Es gibt nur Id und Name. Wenn ich also sage, dass Klaus alle Benutzer ausser Uwe und sich selbst sehen soll und die Tabelle nur Id und Namen her gibt... Es gibt nichts abzufragen ausser Id und Namen deshalb kann ich dein Problem einfach nicht nachvollziehen.

Wenn du eine vollständige Ergebnistabelle lieferst, die du gern hättest, aber nicht hinkriegst, dann kann man damit mehr anfangen als durch irgendwelche alleinigen Delta-Beschreibungen wie "der ist nicht drin, sollte aber"

- Sven Rautenberg

[Werner]

Hallo!

Wenn du eine vollständige Ergebnistabelle lieferst, die du gern hättest, aber nicht hinkriegst, dann kann man damit mehr anfangen als durch irgendwelche alleinigen Delta-Beschreibungen wie "der ist nicht drin, sollte aber"

Ja daran soll es ja nicht scheitern nur weil ich es nicht nachvollziehen kann :)

┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│      Uwe      │ │     Klaus     │ │      Gabi     │
├────────┬──────┤ ├────────┬──────┤ ├────────┬──────┤
│ userId │ name │ │ userId │ name │ │ userId │ name │
├────────┼──────┤ ├────────┼──────┤ ├────────┼──────┤
│ 3      │ Gabi │ │ 3      │ Gabi │ │ 4      │ Inge │
├────────┼──────┤ ├────────┼──────┤ └────────┴──────┘
│ 4      │ Inge │ │ 4      │ Inge │
├────────┼──────┤ ├────────┼──────┤
│ 5      │ Gerd │ │ 5      │ Gerd │
├────────┼──────┤ ├────────┼──────┤
│ 6      │ Sepp │ │ 6      │ Sepp │
└────────┴──────┘ └────────┴──────┘
┌───────────────┐ ┌───────────────┐
│     Gerd      │ │     Sepp      │
├────────┬──────┤ ├────────┬──────┤
│ userId │ name │ │ userId │ name │
└────────┴──────┘ └────────┴──────┘

Da die Gruppe von Inge keinen Zugriff auf das Modul hat, gibt es eigentlich nicht den Fall, dass sie Benutzer überhaupt abfragt.

Beste Grüße!

[Werner]

Da die Gruppe von Inge keinen Zugriff auf das Modul hat, gibt es eigentlich nicht den Fall, dass sie Benutzer überhaupt abfragt.

Oh und dasselbe gilt ja auch für Gerd und Sepp. Die haben ja auch gar keinen Zugriff.

[Werner]

Hallo!

Ich glaube, ich habe dank des posts von Vinzent die Lösung gefunden. Danke für eure Hilfe!

Hier fürs Archiv:

Gegeben sind mir die Gruppen-Ids der Untergruppen von den Gruppen, die Zugriff auf das Modul haben.

  
SELECT  
SQL_CALC_FOUND_ROWS  
    `user`.`name`,  
    `user`.`userId`  
FROM  
    `corre_g_u`  
LEFT JOIN  
    `user` USING(`userId`)  
WHERE  
    `user`.`userId`  
IN  
    (SELECT  
        `corre_g_u`.`userId`  
    FROM  
        `corre_g_u`  
    WHERE  
        `corre_g_u`.`groupId` = ?  
    OR  
        `corre_g_u`.`groupId` = ?  
    OR  
        /* ... hier stehen die Gruppen-Ids die ich bereits habe ... */)		  
AND  
    `user`.`userId`  
NOT IN  
    (SELECT  
        `corre_g_u`.`userId`  
    FROM  
        `corre_g_u`  
    WHERE  
        `corre_g_u`.`groupId` != ?  
    AND  
        `corre_g_u`.`groupId` != ?  
    AND  
        /* ... hier stehen wieder die Gruppen-Ids ... */)  
GROUP BY  
    `user`.`name`  
ORDER BY  
    `user`.`name`  
LIMIT ?, ?;  

Habe ich noch etwas übersehen?

Beste Grüße!

[Vinzenz Mai]

Hallo Werner,

Gegeben sind mir die Gruppen-Ids der Untergruppen von den Gruppen, die Zugriff auf das Modul haben.
Habe ich noch etwas übersehen?

Deine vorgestellte Lösung konnte ich wegen fehlender Parameterwerte nicht nachvollziehen. Im weiteren zeige ich meinen Lösungsvorschlag, der auf meinem Verständnis Deiner Beschreibung basiert.

Ich gehe im folgenden davon aus, dass es genau einen Parameter gibt: die userId des Benutzers, für den wir die Liste der Benutzer sehen wollen, die er administrieren darf (diese ist leer, wenn der Benutzer nicht in einer Gruppe ist, die Zugriff auf die Benutzerverwaltung haben).

a) Liste der echten Untergruppen der Gruppen, in denen sich der Benutzer befindet:

-- Echte Untergruppen haben einen lKey, der größer ist als der lKey
-- der Elterngruppe und kleiner ust als der rKey.
-- Dass Gruppen mehrfach aufgeführt werden, soll uns nicht stören.
-- Erst die Benutzer wollen wir nur noch einfach sehen

SELECT  
    child.groupId  
FROM  
    groups node  
INNER JOIN  
    groups child  
ON  
    child.lKey > node.lKey AND child.lKey < node.rKey  
WHERE  
    -- wir benötigen die Untergruppen aller Gruppen,  
    -- in denen sich der Benutzer befindet  
    node.groupId IN (  
        SELECT  
            groupId  
        FROM  
            group_user  
        WHERE  
            userId = ?  
    )  

Liefert für den userId-Wert 1 (Uwe) als Ergebnis

groupId
-------
    2
    3
    4
    5

/*
    Komplizierter ist das Ausschlussverfahren:
    Hier dürfen nicht einfach alle Vorfahren aller Gruppen
    ausgeschlossen werden, in denen sich der Benutzer befindet,
    eingeschlossen der Gruppe selbst - sondern nur die Vorfahren
    inklusive der Gruppe selbst, die nicht in der Liste der echten
    Untergruppen enthalten sind.

Im Beispiel ist Uwe in den Gruppen 1 und 3 enthalten
    Vorfahren von 1 ist 1,
    Vorfahren von 3 sind 1, 2, 3
    Die Vereinigungsmenge aller Vorfahren enthält somit 1, 2, 3

Echte Untergruppen von 1 sind 2, 3, 4, 5
    Echte Untergruppen von 3 gibt es nicht

Da 2 und 3 in den echten Untergruppen enthalten sind, entfallen sie
    aus der Ausschlussliste:
    Ausgeschlossen werden nur die Mitglieder der Gruppe 1
*/

-- Die Vorfahren aller Gruppen (inklusive der Gruppen selbst)
-- eines Benutzers erhält man über

SELECT  
    ancestor.groupId  
FROM  
    groups node  
INNER JOIN  
    groups ancestor  
ON	  
    node.lKey BETWEEN ancestor.lKey AND ancestor.rKey  
WHERE  
    node.groupId IN (  
        SELECT  
            groupId  
        FROM  
            group_user gu2  
        WHERE  
            gu2.userId = ?  
    )

Liefert für den userId-Wert 1 folgendes Ergebnis

groupId
-------
    1
    1
    2
    3

-- Die Vorfahren aller Gruppen ohne die echten Untergruppen ergibt sich somit
-- aus der Kombination:

SELECT  
    ancestor.groupID  
FROM  
    groups node  
INNER JOIN  
    groups ancestor  
ON	  
    node.lKey BETWEEN ancestor.lKey AND ancestor.rKey  
WHERE  
    node.groupId IN (  
        SELECT  
            groupId  
        FROM  
            group_user gu2  
        WHERE  
            gu2.userId = ?  
    )  
AND  
    node.groupId NOT IN (  
        SELECT  
            child.groupID  
        FROM  
            groups node  
        INNER JOIN  
            groups child  
        ON  
            child.lKey > node.lKey AND child.lKey < node.rKey  
        WHERE  
            node.groupID IN (  
                SELECT  
                    groupId  
                FROM  
                    group_user gu2  
                WHERE  
                    gu2.userId = ?  
            )  
    )  

Liefert für Uwe (userId 1) folgendes Ergebnis

groupId
-------
    1

Setzen wir es zusammen:

SELECT DISTINCT   -- damit jeder Benutzer nur einmal ausgegeben wird  
    u.userId,  
    u.name  
FROM  
    user u  
INNER JOIN  
    group_user gu  
USING  
    (userId)  
WHERE  
    -- Benutzer ist in echten Untergrupen enthalten  
    gu.groupId IN (  
        SELECT  
            child.groupId  
        FROM  
            groups node  
        INNER JOIN  
            groups child  
        ON  
            child.lKey > node.lKey AND child.lKey < node.rKey  
        WHERE  
            node.groupId IN (  
                SELECT  
                    groupId  
                FROM  
                    group_user  
                WHERE  
                    userId = ?  
            )  
    )  
AND  
    -- aber nicht in übergeordneten Gruppen (inklusive dieser selbst)  
    u.userId NOT IN (  
        SELECT  
            gu2.userId  
        FROM  
            group_user gu2  
        WHERE  
            gu2.groupId IN (  
                SELECT  
                    ancestor.groupId  
                FROM  
                    groups node  
                INNER JOIN  
                    groups ancestor  
                ON	  
                    node.lKey BETWEEN ancestor.lKey AND ancestor.rKey  
                WHERE  
                    node.groupId IN (  
                        SELECT  
                            groupId  
                        FROM  
                            group_user gu2  
                        WHERE  
                            gu2.userId = ?  
                    )  
                AND  
                    node.groupId NOT IN (  
                        SELECT  
                            child.groupId  
                        FROM  
                            groups node  
                        INNER JOIN  
                            groups child  
                        ON  
                            child.lKey > node.lKey AND child.lKey < node.rKey  
                        WHERE  
                            node.groupId IN (  
                                SELECT  
                                    groupId  
                                FROM  
                                    group_user gu2  
                                WHERE  
                                    gu2.userId = ?  
                            )  
                    )  
            )  
    )  

Ergänzen wir noch die Prüfung auf die Mitgliedschaft in einer Gruppe, die für die Benutzerverwaltung berechtigt ist:

AND (  
    SELECT  
        (COUNT(groupId) > 0) Admin  
    FROM  
        group_user  
    WHERE  
        userId = ?  
    AND  
        groupId IN (  
            SELECT  
                groupId  
            FROM  
                module_group  
            WHERE  
                moduleId = 1  
        )  
)  
-- und sortieren nach Gruppe  
ORDER BY  
    gu.groupId  

Ergebisse für die userId-Wert 1 und 2 jeweils:

userId name
-----------
    3  Gabi
    4  Inge
    5  Gerd
    6  Sepp

für den userId-Wert 3:

userId name
-----------
    4  Inge

und für alle anderen ein leeres Ergebnis - wie gewünscht.

Die Idee mit implizitem Verbot bei gleichzeitigem "Verbot geht über Erlaubnis" kann zu überraschenden Ergebnissen führen: Hinzufügen zu einer weiteren Gruppe in einem anderen Ast kann effektiv zum Verlust von Berechtigungen führen, d.h. zu geringeren Rechten als vorher. Ist das so gewollt oder habe ich das System falsch verstanden?

Bitte beachte: meine Verknüpfungstabellen haben etwas andere Namen als Deine.

Freundliche Grüße

Vinzenz

[Werner]

Hallo Vinzenz!

Gegeben sind mir die Gruppen-Ids der Untergruppen von den Gruppen, die Zugriff auf das Modul haben.
Habe ich noch etwas übersehen?

Deine vorgestellte Lösung konnte ich wegen fehlender Parameterwerte nicht nachvollziehen.

Meine Beispiele waren nicht ganz 1:1 aus meinem Projekt.
Genau genommen habe ich 2 Parameter. Die Benutzer-Id und eine Content-Id.
Datensätze der Content-Ids enthalten die Modul-Id (hier die Id des Benutzerverwaltungs-Moduls), eine Gruppen-Id und die Rechte als Integer.

Die Idee mit implizitem Verbot bei gleichzeitigem "Verbot geht über Erlaubnis" kann zu überraschenden Ergebnissen führen: Hinzufügen zu einer weiteren Gruppe in einem anderen Ast kann effektiv zum Verlust von Berechtigungen führen, d.h. zu geringeren Rechten als vorher. Ist das so gewollt oder habe ich das System falsch verstanden?

Die Rechte für das Modul sind nicht abhängig von der Gruppe sondern davon welche dem "content" gegeben wurden.

Angenommen 2 Benutzer sind beide in den Gruppen A und B.
A hat aber nur zugriff auf das Modul, dann werden nur die Benutzer angezeigt die ausschließlich in Untergruppen von A sind.
Haben A und B zugriff auf das Modul, werden beiden Benutzern alle Benutzer angezeigt die in Untergruppen von A _oder_ B sind aber nicht in anderen Gruppen inkl. A und B sind.

Der Nachteil daran ist, dass ich, will ich verschiedene Rechte für A und B, das Modul 2 mal in die Seite bauen muss und Benutzer beider Gruppen dann auch 2 Module angezeigt bekommen. Beispielsweise A darf löschen, B darf bearbeiten. Es sind immernoch 2 Benutzer in beiden Gruppen. Dann bekommt A die Zuweisung zur content-Id mit den entsprechenden Rechten und Verweis auf das entsprechende Modul. B bekommt eine Zuweisung mit anderen Rechten.

Die Benutzerverwaltung für A wird dann nur Untergruppen von A anzeigen und diese als löschbar markieren. Für B werden entsprechend nur die Benutzer dessen Untergruppen angezeigt und als bearbeitbar markiert.
Da aber beide Benutzer in beiden Gruppen sind, werden ihnen auch beide Verwaltungen angezeigt.

Ich bin mir aber ehrlich gesagt selbst nicht sicher wie gut diese Lösung ist. Ich sitze mittlerweile schon so lange daran und das war einfach die beste Lösung die mir bisher eingefallen ist.

Im weiteren zeige ich meinen Lösungsvorschlag, der auf meinem Verständnis Deiner Beschreibung basiert.

Danke erstmal dafür. Das erschlägt mich ehrlich gesagt gerade ein wenig. Ich brauch wohl etwas bis ich das alles nachvollzogen habe.
Was meinst du z.B. mit "_echten_ Untergruppen"?

a) Liste der echten Untergruppen der Gruppen, in denen sich der Benutzer befindet:

-- Echte Untergruppen haben einen lKey, der größer ist als der lKey
-- der Elterngruppe und kleiner ust als der rKey.

Achso ja. Wie gesagt, die Abfrage dafür habe ich. Ich habe die Ids aller "echten Untergruppen".

/*
    Komplizierter ist das Ausschlussverfahren:
    Hier dürfen nicht einfach alle Vorfahren aller Gruppen
    ausgeschlossen werden, in denen sich der Benutzer befindet,
    eingeschlossen der Gruppe selbst - sondern nur die Vorfahren
    inklusive der Gruppe selbst, die nicht in der Liste der echten
    Untergruppen enthalten sind.

Im Beispiel ist Uwe in den Gruppen 1 und 3 enthalten
    Vorfahren von 1 ist 1,
    Vorfahren von 3 sind 1, 2, 3
    Die Vereinigungsmenge aller Vorfahren enthält somit 1, 2, 3

Echte Untergruppen von 1 sind 2, 3, 4, 5
    Echte Untergruppen von 3 gibt es nicht

Da 2 und 3 in den echten Untergruppen enthalten sind, entfallen sie
    aus der Ausschlussliste:
    Ausgeschlossen werden nur die Mitglieder der Gruppe 1
*/

Wären dann aber nicht Benutzer die auch in Gruppen anderer Äste sind auch gelistet?

Nehmen wir 1 Mitarbeiter der in 2 verschiedenen Abteilungen arbeitet. Beide Abteilungen haben ihre eigene Personalverwaltung. Dann wäre der Mitarbeiter von beiden Personalverwaltungen "komplett kündbar". Soll das so sein oder habe ich da etwas falsch verstanden?

Beste Grüße!