{MYSQL} JOINS über mehrer Tabellen

[Siramon]

hallo forum,

ich arbeite gerade an einem authorisations-system für meine website.
dort muss sich der user anmelden und kriegt eine rolle.

beispiele: gast, member, moderator, administrator...

jede website, die sich der user ankucken will lässt eine oder mehrer rollen zu.

ich hab mir dafür eine tabellenstruktur zusammengebastelt:

Sites
-----
SID (die ID der sites - primary key)
Sitename
usw.

User
----
UID (die ID der mitglieder - primary key)
Nickname
usw.

Roules
----
RID (die ID der rollen - primary key)
Bezeichnung

Zuweisungstabellen:

UserRoules
----
UID (primary key) --> User.UID
RID (primary key) --> Roules.RID

SiteRoules
----
SID (primary key) --> Sites.SID
RID (primary key) --> Roules.RID

ich hoffe, die struktur ist einigermassen verständlich erklärt ;)

die abfrag ist in worten eigentlich ganz simpel und sollte der logik nach auf funktionieren.

ich schreibs mal als sql - statement nieder:

SELECT s.SID, u.UID, sr.SID, sr.RID, ur.UID, ur.RID FROM Sites AS s, User AS u, SiteRoules AS sr, UserRoules AS ur WHERE u.UID = ur.UID AND s.SID = sr.SID AND ur.RID = sr.RID

das klappt eigentlich so auch, das heisst, wenn die abfrage daten zurückliefert, ist der benutzer berechtigt die site zu sehen, wenn nicht --> keine berechtigung.

mein problem ist nun die performance. das karthesische produkt dieser 4 tabellen könnte in den himmel wachsen (mein gott, denk ich blumig ;).

ich suche jetzt also nach einer lösung mit JOINS, ich habe mir da schon ein paar sachen angeschaut.

z.b. dies hier:

SELECT A.EineSpalte, B.EineAndereSpalte  
FROM Tabelle1 AS A JOIN Tabelle2 AS B      
ON A.EinWert = B.EinAndererWert;

aber wie funktioniert das ganze nun über mehr als zwei tabellen? habe leider dazu nix gefunden...

ich habe schon mehrer möglichkeiten selbst ausprobiert, aber ich kriegs nicht auf die reihe :(

wäre froh, wenn mir jemand ein rezept oder einen hinweis geben könnte.

tx a lot im voraus.

llap

Siramon

[Ludwig Ruderstaller]

Hallo,

select * from tabel1,table2.....

==

Select * from Tabelle1 JOIN Tabelle2....

Egal wie du es schreibst es ist exakt das gleiche.

lg
Ludwig

BTW. das hat nix mit PHP zu tun sondern mit SQL also bitte nächstes mal richtige kategorie wählen. (wenn du eine grafik in html einbindest gibst als kategorie auch html an und nicht Photoshop!) :-)

[Siramon]

hallo Ludwig

select * from tabel1,table2.....

==

Select * from Tabelle1 JOIN Tabelle2....

Egal wie du es schreibst es ist exakt das gleiche.

äh nicht ganz oder? also, das ergebnis wird gleich sein, aber die performance ist beim zweiten erheblich besser!

also was ich probiert habe schreib ich doch noch hin ;)

SELECT s.SiteID, u.MID, sr.SiteID, sr.RID, ur.MID, ur.RID
FROM sites AS s JOIN siteroules AS sr JOIN userroules AS ur JOIN mitglieder AS u
ON u.UID=ur.UID AND s.SiteID=sr.SiteID AND ur.RID=sr.RID;

ok, das wäre meine lösung, die aber leider so nicht funzt.
du siehst mein problem ist, dass ich nicht weiss, wie ich joins über mehrere tabellen machen kann.

mit zwei tabellen und einem join funzt alles supi!

ich habe mir auch überlegt, dass ich zwei temporäre tabellen anlegen könnte und am schluss diese beiden abfrage, aber wenn sich das irgendwie vermeiden lässt, äh möchte ich es vermeiden (*g* dummes deutsch)

mfg

Siramon

BTW. das hat nix mit PHP zu tun sondern mit SQL also bitte nächstes mal richtige kategorie wählen. (wenn du eine grafik in html einbindest gibst als kategorie auch html an und nicht Photoshop!) :-)

hmm, dieses beispiel gefällt mir - trifft den nagel voll auf den kopp.

[Klaus Mock]

Hallo

SELECT s.SID, u.UID, sr.SID, sr.RID, ur.UID, ur.RID FROM Sites AS s, User AS u, SiteRoules AS sr, UserRoules AS ur WHERE u.UID = ur.UID AND s.SID = sr.SID AND ur.RID = sr.RID

[...]
mein problem ist nun die performance. das karthesische produkt dieser 4 tabellen könnte in den himmel wachsen (mein gott, denk ich blumig ;).

Soweit ich Dein Statement richtig interpretiere, _willst_ Du eine Unmenge von Daten sehen. Du willst  ja jede Kombination aus den angegebenenwerten sehen. Da kann dann schon was zusammenkommen.
Eigentlich ist es ein Kreuzprodukt von den angebenen Tabellen (naja nicht ganz wahrscheinlich)
Da helfen auch keine JOINS, weil Du willst ja alles sehen.

Wenn ich Dich richtig verstehe, was natürlich nicht der Fall sein kann, dann willst Du eigentlich alle Rollen wissen, welche aktuell gültig sind.
also:

Ich postuliere mal einige Variablen (PHP ist da gleich wie Perl, oder irre ich mich, naja egal, Du wirst es schon lesen können, alle Dinge mit einem '$' vorn sind die Variablen)

Dann könnte das Statement so aussehen:

(Ich mach mal aus 'Roules' 'Roles', weils besser aussieht ;-) )
SELECT r.RID
FROM Roles r, UserRoles ur, SiteRoles sr
WHERE r.RID = ur.RID
AND   r.RID = sr.RID
AND   ur.UID = $AktuelleUID
AND   sr.SID = $AktuelleSID

Nun zum Thema Performance:
Das ist jetzt natürlich datenbankabhängig, aber so krass wirds nicht sein, denk ich mir, wenn Du ein solches Statement absetzt, und die Indizes halbwegs sauber aufgebaut sind.

Noch ein Tip: verwendet nicht blind DISTINCT, um doppelte Datensätze zu vermeiden. Versuche zuerst ein passendes Statement zu formulieren, das auch ohne DISTINCT auskommt. Weil _das_ geht wirklich mächtig auf die Performance, ohne daß Du es sofort bemerkst. Das DISTINCT greift nämlich meist erst nach der DB-internen Abfrage, und das ist der Schaden schon passiert.

So, ich hoffe, ich bin nicht zu weit weg mit meiner Vermutung

Grüße
  Klaus

[Siramon]

hallo klaus

Soweit ich Dein Statement richtig interpretiere, _willst_ Du eine Unmenge von Daten sehen. Du willst  ja jede Kombination aus den angegebenenwerten sehen. Da kann dann schon was zusammenkommen.
Eigentlich ist es ein Kreuzprodukt von den angebenen Tabellen (naja nicht ganz wahrscheinlich)
Da helfen auch keine JOINS, weil Du willst ja alles sehen.

hmm ich habe mich da so auf die frage konzentriert, wie ich das mit den joins lösen soll, dass ich den sql-ausdruck versaut habe. natürlich sollte das ganze mit dem aktuellen benutzer und der aktuellen site passieren.

Wenn ich Dich richtig verstehe, was natürlich nicht der Fall sein kann, dann willst Du eigentlich alle Rollen wissen, welche aktuell gültig sind.
also:
Ich postuliere mal einige Variablen (PHP ist da gleich wie Perl, oder irre ich mich, naja egal, Du wirst es schon lesen können, alle Dinge mit einem '$' vorn sind die Variablen)

siehe oben

Dann könnte das Statement so aussehen:

(Ich mach mal aus 'Roules' 'Roles', weils besser aussieht ;-) )

äh peinlich *g*

SELECT r.RID

»»  FROM Roles r, UserRoles ur, SiteRoles sr
»»  WHERE r.RID = ur.RID
»»  AND   r.RID = sr.RID
»»  AND   ur.UID = $AktuelleUID
»»  AND   sr.SID = $AktuelleSID

genauso hab ichs mir eigentlich gedacht, d.h. soweit war ich mit dem ganzen: tabellen mit der WHERE klausel zu verknüpfen.

ich kann das ganze mal in zwei selects darstellen...
das würde dann für mein beispiel so aussehen:

SELECT sites.SiteID, siteroles.SiteID, siteroles.RID
FROM sites LEFT JOIN siteroles USING (SiteID)
WHERE sites.SiteID = $AktuelleSID;

SELECT mitglieder.UID, userroles.UID, userroles.RID
FROM mitglieder LEFT JOIN userroles USING (UID)
WHERE mitglieder.UID = $AktuelleUID;

Diese zwei Selects müssten nun via RID verknüpft werden:

SELECT1 JOIN SELECT2 USING (RID); (das ist natürlich syntaktisch falsch)

geht das irgendwie zusammen, oder muss ich da temporäre tabellen anlegen?

vielen dank

llap

Siramon

[Siramon]

hallo ludwig, hallo klaus, hallo forum,

ich habe die lösung gefunden - und demnach auch meinen überlegungsfehler ;)

kurz nochmals das problem:

ich arbeite gerade an einem authorisations-system für meine website.
dort muss sich der user anmelden und kriegt eine rolle.

beispiele: gast, member, moderator, administrator...

jede website, die sich der user ankucken will lässt eine oder mehrer rollen zu.

ich hab mir dafür eine tabellenstruktur zusammengebastelt:

Sites

SID (die ID der sites - primary key)
Sitename
usw.

User

UID (die ID der mitglieder - primary key)
Nickname
usw.

Roles

RID (die ID der rollen - primary key)
Bezeichnung

Zuweisungstabellen:

UserRoles

UID (primary key) --> User.UID
RID (primary key) --> Roles.RID

SiteRoles

SID (primary key) --> Sites.SID
RID (primary key) --> Roles.RID

die lösung:

SELECT SiteRoles.RID
FROM
sites LEFT JOIN SiteRoles USING (SID)
LEFT JOIN UserRoles USING (RID)
LEFT JOIN User USING (UID)
WHERE User.UID = $AktuelleUID AND sites.SID = $AktuelleSID;

vielleicht könnte man das noch mehr optimieren?

aber auf jedenfall vielen dank für euere tips!

llap

Siramon

[Bio]

Sup!

Sag mal, hat MySQL keine Anfrgageoptimierung?
Jeder "bessere" Datenbank hat normalerweise recht trickreiche Verfahren implementiert, die den Aufwand einer Anfrage ganz automatisch minimieren.

Gruesse,

Bio

[Siramon]

hallo Bio

Sag mal, hat MySQL keine Anfrgageoptimierung?
Jeder "bessere" Datenbank hat normalerweise recht trickreiche Verfahren implementiert, die den Aufwand einer Anfrage ganz automatisch minimieren.

schau dir mal diese seite an: http://www2.little-idiot.de/mysql/mysql-127.html

es gibt da gewisse möglichkeiten, dass dir mysql hilft den query zu optimieren.

llap

Siramon