MySQL SELECT Abfrage gesucht

[Hugo Egon Balder]

Hallo Forum,

ich würde gerne wissen, ob es für folgendes Ziel eine halbwegs einfache MySQL SELECT-Anweisung gibt:

In einer DB werden zunächst verschiedene Gruppen mit einer ID definiert:

Tabelle 'gruppen':

id | gruppe
 -----------
 1  | Familie
 2  | Freunde
 3  | Bekannte
 4  | Gäste
 5  | Admin

In einer weiteren Tabelle werden User, die zu mindestens einer oder zu mehreren Gruppen gehören können, eingetragen:

Tabelle 'user':

id | name             | gruppen
 -------------------------------
 1  | Hans Bacher      | 1, 2, 3
 2  | Claudia Blum     | 1, 2, 3
 3  | Max Mustermann   | 2, 3
 4  | Tobias Huber     | 3
 5  | Hans Gockel      | 2, 3
 6  | Ferdinand Müller | 4
 7  | Hugo Egon Balder | 1, 2, 3, 4, 5

In eine dritten Tabelle kommen nun verschiedene Artikeln sowie jene Gruppen, die eine Leseberechtigung für den jeweiligen Artikel haben:

Tabelle 'artikeln':

id | titel              | gruppenerlaubnis
 ------------------------------------------
 1  | Familienfotos      | 1, 5
 2  | Ein Gedicht        | 1, 2, 3, 5
 3  | Restaurantkritik   | 1, 2, 3, 4, 5
 4  | Berlinfotos        | 1, 2, 3, 4, 5
 5  | Meine Liebschaften | 5
 6  | Foo                | 2
 7  | Blaa               | 3

Nun möchte ich, nachdem sich ein User eingeloggt hat (also die id der Tabelle 'user' ist gegeben), alle Artikeln auflisten, die für die Gruppen des Users freigegeben sind.

Wenn also zB die id '3' für Max Mustermann gegeben ist, sollten die Artikeln 2, 3, 4, 6, 7 aufgelistet werden, weil diese entweder für eine oder beide der Gruppen, denen der User zugeordnet ist, freigegeben sind.

Wie würdet Ihr das lösen? Irgendwas in die Richtung … WHERE FIND_IN_SET(…) … vielleicht?
Oder soll ich eine völlig andere Tabellenstruktur nehmen?

Danke im Voraus für jeden Input!

Mit freundlichen Grüßen

Hugo Egon Balder

[MudGuard]

Hi,

id | name             | gruppen

1  | Hans Bacher      | 1, 2, 3

Hier ist m.E. das Problem.

Die Gruppenzugehörigkeit gehört nicht in die User-Tabelle (da nicht 1:1), sondern in eine UserGruppen-Tabelle:

userid | gruppenid
-------------------
1      | 1
1      | 2
1      | 3
3      | 2
3      | 3
...

id | titel              | gruppenerlaubnis

1  | Familienfotos      | 1, 5

Dito, die gruppenerlaubnis gehört in eine Extra-Tabelle.

Dann läßt sich das ganze über einfache joins lösen, ohne daß man erst die bei Dir in eine Spalte gepreßten Werte extrahieren muß.

cu,
Andreas

[dedlfix]

Tach!

Dann läßt sich das ganze über einfache joins lösen, [...]

Mit einfachen Joins ergibt es mehrfach denselben Artikel, wenn User und Artikel mehrfach in denselben Gruppen vorkommen. Das lässt sich zwar mit DISTINCT wegfiltern, wenn man sich für die Ergebnis-Menge auf Felder der Artikel-Tabelle beschränkt, aber schön ist das nicht unbedingt.

dedlfix.

[dedlfix]

Tach!

Wie würdet Ihr das lösen? Irgendwas in die Richtung … WHERE FIND_IN_SET(…) … vielleicht?

Ja. Das einzige was noch ginge, wäre Stringverarbeitung. Alles beides ist aber nur bei kleinen Datenmengen akzeptabel, weil für die Suche kein Index verwendet werden ksnn und somit immer die gesamten Tabellen durchsucht werden müssen.

Zudem musst du pro User-Gruppe eine eigene Abfrage stellen, weil es kein FIND_IN_SET() mit mehreren zu suchenden Werten gibt.

Oder soll ich eine völlig andere Tabellenstruktur nehmen?

Besser wäre das. Ordentliche Relationen in einem relationalen DBMS lassen sich besser handhaben als Teilstring-Relationen (oder Teilwerte in Feldern).

Mit extra User-Gruppen- und Gruppen-Artikel-Tabellen (jeweils m:n-Beziehung) kann man eine Abfrage mit zwei geschachtelten Subquerys (oder drei, wenn man vom Usernamen statt der ID ausgeht) schreiben: Wähle alle Artikel, deren ID sich in den Artikel-IDs der Artikel-Gruppentabelle befindet, deren Gruppen-IDs sich in der auf die User-ID eingeschränkte Menge der IDs der User-Gruppentabelle befindet. Oder villeicht etwas einfacher formuliert, von innen nach außen: Wähle alle Gruppen-IDs aus der User-Gruppentabelle für die User-ID des gesuchten Users. Wähle alle Artikel-IDs aus der Artikel-Gruppen-Tabelle deren Gruppen-ID in der Ergebnismenge der vorhergehenden Abfrage ist. Wähle alle Artikel, deren ID in der Ergebnismenge der vorherigen Frage ist.

dedlfix.

[Hugo Egon Balder]

Hi dedlfix,

zunächst ein mal danke fürs Antworten! (Auch Dir, MudGuard!)

Mit extra User-Gruppen- und Gruppen-Artikel-Tabellen (jeweils m:n-Beziehung) kann man eine Abfrage mit zwei geschachtelten Subquerys (oder drei, wenn man vom Usernamen statt der ID ausgeht) schreiben:

verstehe ich Dich richtig, dass das dann auf 5 Tabellen rausläuft?

1.) Tabelle 'user':

id | name
---------------------
1  | Hans Bacher
2  | Claudia Blum
3  | Max Mustermann
4  | Tobias Huber
5  | Hans Gockel
6  | Ferdinand Müller
7  | Hugo Egon Balder

2.) Tabelle 'gruppen':

id | gruppe
-------------
1  | Familie
2  | Freunde
3  | Bekannte
4  | Gäste
5  | Admin

3.) Tabelle 'user_in_gruppen':

id | user | gruppe
------------------
1  | 1    | 1
2  | 1    | 2
3  | 1    | 3
4  | 2    | 1
5  | 2    | 2
6  | 2    | 3
7  | 3    | 2
8  | 3    | 3
u.s.w. [...]

4.) Tabelle 'artikeln':

id | titel
-----------------------
1  | Familienfotos
2  | Ein Gedicht
3  | Restaurantkritik
4  | Berlinfotos
5  | Meine Liebschaften
6  | Foo
7  | Blaa

5.) Tabelle 'artikel_fuer_gruppen':

id | artikel | gruppe
---------------------
1  | 1       | 1
2  | 1       | 5
3  | 2       | 1
4  | 2       | 2
5  | 2       | 3
6  | 2       | 5
7  | 3       | 1
8  | 3       | 2
u.s.w. [...]

Aber das ist doch exakt das, was auch MudGuard vorgeschlagen hat. Und das hast Du doch kritisiert! Oder bezog sich Deine Kritik nicht auf die Art der Tabellen sondern darauf, dass er mit Joins arbeiten würde?

etwas einfacher formuliert, von innen nach außen: Wähle alle Gruppen-IDs aus der User-Gruppentabelle für die User-ID des gesuchten Users. Wähle alle Artikel-IDs aus der Artikel-Gruppen-Tabelle deren Gruppen-ID in der Ergebnismenge der vorhergehenden Abfrage ist. Wähle alle Artikel, deren ID in der Ergebnismenge der vorherigen Frage ist.

OK, ich bilde mir zwar ein, dass jetzt nach mehrmaligem Lesen von der Logik her verstanden zu haben, wie ich das jetzt mit Subqueries verwirklichen soll, ist mir leider nicht klar, da ich noch nie mit Subqueries gearbeitet habe.

Vorsausgesetzt, ich habe Dich, was die Tabellen betrifft, richtig verstanden und ich möchte jetzt alle Artikeln aufgelistet bekommen, die für den User mit der ID '3' freigegeben sind (also sprich für die Gruppen, deren "Mitglied" er ist) ... wie müsste dann eine SELECT Anweisung aussehen?

Mit freundlichen Grüßen

Hugo Egon Balder

[dedlfix]

Tach!

verstehe ich Dich richtig, dass das dann auf 5 Tabellen rausläuft?

Ja. 3 Tabellen für die Daten und zwei für die m:n-Beziehungen. Das ist die übliche Vorgehensweise bei m:n.

Aber das ist doch exakt das, was auch MudGuard vorgeschlagen hat. Und das hast Du doch kritisiert! Oder bezog sich Deine Kritik nicht auf die Art der Tabellen sondern darauf, dass er mit Joins arbeiten würde?

Erstens ja, und zweitens war meine Aussage zur Join-Lösung, dass diese potentiell zu viele Ergebnisse liefert, die man erst noch wegfiltern muss. Man kommt damit zum Ziel, aber mit Abzügen in der B-Note. Möglicherwiese hat diese Vorgehenswiese auch noch Nachteile bei großen Datenmengen, weil ein zu größes Zwischenergebnis produziert wird, falls der Optimizer nicht eingreift.

etwas einfacher formuliert, von innen nach außen: Wähle alle Gruppen-IDs aus der User-Gruppentabelle für die User-ID des gesuchten Users. Wähle alle Artikel-IDs aus der Artikel-Gruppen-Tabelle deren Gruppen-ID in der Ergebnismenge der vorhergehenden Abfrage ist. Wähle alle Artikel, deren ID in der Ergebnismenge der vorherigen Frage ist.

OK, ich bilde mir zwar ein, dass jetzt nach mehrmaligem Lesen von der Logik her verstanden zu haben, wie ich das jetzt mit Subqueries verwirklichen soll, ist mir leider nicht klar, da ich noch nie mit Subqueries gearbeitet habe.

Mach das mal schrittweise nach. Der erste Satz sollte nicht zu schwer sein. Die Query lass laufen und schau, dass sie das richtige Zwischenergebnis liefert. (Das ist auch noch ein Vorteil. Beim Join hat man nur einen großen Batzen, bei Subquerys kann man die Zwischenergebnisse einzeln testen. Also, nicht in jedem Fall, aber hier geht das.) Dann leg die Query kurz beiseite. Fang an mit dem nächsten Satz und beachte das unscheinbare Wörtchen "in". Das wird zu einem IN(...) und in die Klammern kommt die Subquery. Der dritte Satz folgt demselben Prinzip.

Vorsausgesetzt, ich habe Dich, was die Tabellen betrifft, richtig verstanden und ich möchte jetzt alle Artikeln aufgelistet bekommen, die für den User mit der ID '3' freigegeben sind (also sprich für die Gruppen, deren "Mitglied" er ist) ... wie müsste dann eine SELECT Anweisung aussehen?

Nun, das ist deine Hausaufgabe :-) Die Möglichkeiten von Subquerys sind recht vielfältig und entsprechend umfangreich das Subquery-Kapitel im MySQL-Handbuch. Aber eine Subquery im IN() ist vergleichsweise einfach zu verstehen. Statt einer kommaseparierten Menge einzelner Werte steht hier eine Subquery, die eine Ergebnismenge der Werte eines Feldes einer Tabelle liefert - zumindest in deinem Fall, ansonsten kann man das noch beliebig komplexer gestalten.

dedlfix.

[Hugo Egon Balder]

Hi dedlfix,

Nun, das ist deine Hausaufgabe :-)

es ist echt eine Frechheit von Dir, bei Aussentemperaturen von über 31 Grad Hausaufgaben zu geben, anstatt mir einfach die fertige Lösung hinzuschreiben! ;-) *g*

Danke, dass Du versucht hast, mir das halbwegs verständlich näherzubringen und es _könnte_ sein, dass ich es richtig verstanden habe. Deshalb möchte ich hier zu meiner Kontrolle Step by Step schreiben, was ich getan habe, um zu sehen, ob ich Dich eh richtig interpretiert habe:

Mach das mal schrittweise nach. Der erste Satz sollte nicht zu schwer sein. Die Query lass laufen und schau, dass sie das richtige Zwischenergebnis liefert.

Der erste Satz war:

Wähle alle Gruppen-IDs aus der User-Gruppentabelle für die User-ID des gesuchten Users.

Mein Query schaut so aus:

SELECT ug.gruppe
FROM user_in_gruppen AS ug
WHERE ug.user = 3

...und liefert (völlig korrekt) folgendes Ergebnis:

gruppe
------
2
3

Das stimmt. Der User 3 gehört zu den Gruppen 2 und 3.

Der 2. Satz war:

Wähle alle Artikel-IDs aus der Artikel-Gruppen-Tabelle deren Gruppen-ID in der Ergebnismenge der vorhergehenden Abfrage ist.

Und dazu sagtest Du bei Deiner letzten Antwort, ich soll das mit IN(...) lösen. Das war mir etwas klarer, als ich mir ein Beispiel dazu im Manual angesehen habe.

Ich schreibe also einen neuen Query, wo ich den funktionierenden der ersten Zeile als IN(...)-Bedingung einbaue. Mein Query schaut jetzt so aus:

SELECT ag.artikel
FROM artikel_fuer_gruppen AS ag
WHERE ag.gruppe IN ( SELECT ug.gruppe
                                  FROM user_in_gruppen AS ug
                                  WHERE ug.user = 3 )

...und liefert (völlig korrekt) folgendes Ergebnis:

artikel
-------
2
2
3
3
4
4
6
7

Zuerst habe ich nicht verstanden, wieso da die Artikeln 2, 3 und 4 doppelt gelistet werden. Aber es ist, wenn man nachdenkt, eh klar. Ein Mal werden die Artikel-IDs ausgegeben, weil sie für die Usergruppe 2 freigegeben sind und ein Mal, weil sie gleichzeitig auch für die Usergruppe 3 freigegeben sind. Also wieder ein richtiges Ergebnis.

Da ich natürlich keinen Artikel doppelt ausgeben möchte, kommt jetzt Dein 3. Satz ins Spiel:

Wähle alle Artikel, deren ID in der Ergebnismenge der vorherigen Frage ist.

Also auf gut Deutsch: Alle IDs, die bei der 2. Abfrage gefunden worden sind, _ein_ Mal ausgeben. Ich halte mich wieder an Deine mir vorgegebene Vorgehensweise mit dem IN(...) und baue wiederum den soeben verwendeten Query als IN(...)-Bedingung ein. Mein Query schaut jetzt so aus:

SELECT a.id, a.artikelname
FROM artikeln AS a
WHERE a.id IN ( SELECT ag.artikel
                FROM artikel_fuer_gruppen AS ag
                WHERE ag.gruppe IN ( SELECT ug.gruppe
                                     FROM user_in_gruppen AS ug
                                     WHERE ug.user = 3 ))

...und liefert (völlig korrekt) folgendes Ergebnis:

id | artikelname
----------------
2  | Ein Gedicht
3  | Restaurantkritik
4  | Berlinfotos
6  | Foo
7  | Bar

Und das ist _genau_ das, was ich wollte. Es werden exakt jene Artikeln Ausgegeben, die der User mit der id '3' auf Grund seiner Gruppenzugehörigkeiten sehen darf.

War das so in Deinem Sinne und habe ich meine Hausaufgabe jetzt so erfüllt, wie Du es Dir gewünscht hast?

Mit freundlichen Grüßen

Hugo Egon Balder

[dedlfix]

Tach!

Und das ist _genau_ das, was ich wollte. Es werden exakt jene Artikeln Ausgegeben, die der User mit der id '3' auf Grund seiner Gruppenzugehörigkeiten sehen darf.
War das so in Deinem Sinne und habe ich meine Hausaufgabe jetzt so erfüllt, wie Du es Dir gewünscht hast?

Ja. Und für die ausführliche Darstellung des Lösungsweges gibts ein Fleißbienchen :-)

dedlfix.

[Hugo Egon Balder]

Dann möchte ich mich vielmals für die Hilfestellung bedanken!

Ich denke, dieser Thread war jetzt ein gutes Beispiel für Hilfe zur Selbsthilfe, bei der ein Fragender versucht, konstruktiv an einem Problem mitzuarbeiten und eine daraufhin funktionierende und selbst erarbeitete Lösung (wenn auch ungewöhnlich schnell in diesem Fall), bei der ich sicher mehr gelernt habe, als hätte ich eine gepostete Lösung mit COPY & PAST weiterverwendet.

Mit freundlichen Grüßen

Hugo Egon Balder

PS:

Ja. Und für die ausführliche Darstellung des Lösungsweges gibts ein Fleißbienchen :-)

Ich habe vor ca. 35 Jahren als Volksschulkind immer goldene Sterne von der Frau Lehrerein bekommen. Aber ich denke, was immer auch "Fleißbienchen" sein mögen, es ist was Adäquates.

[dedlfix]

Tach!

Noch ein paar Anmerkungen eher kosmetischer Natur. Die kannst du letzlich auch ignorieren, denn so wie du das hast, ist es nicht weniger gut.

SELECT ug.gruppe
FROM user_in_gruppen AS ug
WHERE ug.user = 3

Beim Alias ist es nicht erforderlich (und in einigen SQL-Dialekten auch nicht vorgesehen), das Schlüsselwort AS zu notieren.

Hier ist sogar das komplette Alias ug entbehrlich. (Ebenso das ag auf der nächsten Ebene.) Das ist nur unnötige Tipparbeit und es ergibt sich auch im weiteren Verlauf keine Notwendigkeit, durch das Alias für Klarheit zu sorgen. Die Subquerys bilden hier komplette und eigenständige Einheiten und die Bezeichner sind innerhalb dieser bereits durch ihren Feldnamen eindeutig.

(Der nächste Teil hat keinen Bezug zu deinem Anwendungsfall.)
Etwas anders wäre die Sachlage, wenn es sich um eine korrellierte Subquery handelte. Dabei wird in der Subquery für die WHERE-Bedingung ein Feld aus der Haupt-Query hinzugezogen. Das ist der Fall, wenn zum Beispiel der User nicht ein fester Wert sein soll, sondern der Wert aus einem Feld der Hauptquery sein soll. Konkretes Beispiel: Gib mir alle Artikel(-ID), zu denen (keine) User existieren.

SELECT artikel FROM artikel_fuer_gruppen WHERE EXISTS (SELECT * FROM user_in_gruppen WHERE gruppe = artikel_fuer_gruppen.gruppe)

Hier wird die Subquery für jeden Datensatz der artikel_fuer_gruppen ausgeführt. Beim IN() reicht eine einmalige Ausführung. Für den "keine"-Fall wäre das EXISTS durch ein NOT EXISTS auszutauschen. Statt des * kann auch was beliebig anderes stehen. Die Feldliste im Subquery-SELECT wird bei EXISTS ignoriert. Deswegen selektiert auch ein * nicht zu viel. Aber zurück zum Alias. Das Feld "gruppe" ist zweimal vorhanden. Es reicht jedoch, das aus der äußeren Query eindeutig anzusprechen. (Hier mit dem Tabllennamen, ein Alias wäre jedoch auch ok.) Das alleinstehende "gruppe" bezieht sich auf die nächstliegende Tabelle, also hier user_in_gruppen.

Und dazu sagtest Du bei Deiner letzten Antwort, ich soll das mit IN(...) lösen. Das war mir etwas klarer, als ich mir ein Beispiel dazu im Manual angesehen habe.

Ich hätte da auf [http://dev.mysql.com/doc/refman/5.6/en/any-in-some-subqueries.html@title=Subqueries with ANY, IN, or SOME] nachgeschaut und nicht im Optimizer-Kapitel, aber hat ja offensichtlich auch geholfen.

Zuerst habe ich nicht verstanden, wieso da die Artikeln 2, 3 und 4 doppelt gelistet werden. Aber es ist, wenn man nachdenkt, eh klar. Ein Mal werden die Artikel-IDs ausgegeben, weil sie für die Usergruppe 2 freigegeben sind und ein Mal, weil sie gleichzeitig auch für die Usergruppe 3 freigegeben sind.

Oder anders gesagt: Es gibt in der Tabelle artikel_fuer_gruppen Datensätze für die Gruppe 2 und welche für die Gruppe 3. Beide Datensatzmengen findest du im Ergebnis. Klarer sichtbar wird das, wenn du in der Kontroll-Ausgabe zusätzlich zur Artikel-ID auch noch die Gruppen-ID ausgibst (oder einfach * scheibst).

Da ich natürlich keinen Artikel doppelt ausgeben möchte, kommt jetzt Dein 3. Satz ins Spiel:

Wähle alle Artikel, deren ID in der Ergebnismenge der vorherigen Frage ist.

Beim IN() ist es egal, wie oft dort ein Wert drinsteht, Hauptsache er ist mindestens einmal vorhanden.

Hmm, eben hab ich noch bei einer Query angemerkt, dass sie zu viele Zwischenergebnisse erzeugt und jetzt hab ich selbst den Fall. Wie red ich mich da jetzt raus? Vielleicht so: Hier werden nur ein paar "kleine" ID-Werte und keine kompletten Ergebnisdatensätze erzeugt, da fallen die überflüssigen Werte nicht so sehr ins Gewicht.

dedlfix.

[Hugo Egon Balder]

Hi dedlfix,

Beim Alias ist es nicht erforderlich (und in einigen SQL-Dialekten auch nicht vorgesehen), das Schlüsselwort AS zu notieren.

ich weiß. Ich finde es einfach übersichtlicher so. Ich persönlich erkenne den Sinn hinter dem Query mit dem AS beim kurzen Hinsehen schneller als ohne.

Hier ist sogar das komplette Alias ug entbehrlich. (Ebenso das ag auf der nächsten Ebene.) Das ist nur unnötige Tipparbeit und es ergibt sich auch im weiteren Verlauf keine Notwendigkeit, durch das Alias für Klarheit zu sorgen.

Ich hab mir diese Schreibweise angewöhnt, als ich angefangen habe, mich mit Joins zu befassen und das ist mir seither so in Fleisch und Blut übergegangen, dass ich das _immer_ in dieser Syntax schreibe. Mir ist bewußt, dass das nicht nötig wäre und zusätzliche Tipparbeit ist aber ich finde es einfach sympathischer mit der Aliasschreibung.

Mit freundlichen Grüßen

Hugo Egon Balder