[Erledigt] Hilfestellung beim Erstellen eines etwas komplexeren Queries

Daniel · 22.12.2011, 02:06

Hallo php.de Gemeinde,

ich habe mal wieder ein kleines Problem. Für mein Projekt muss ich ein etwas komplexeren Query erstellen, wo mir im Moment leider total die Idee fehlt. Im Anhang ein Ausschnitt der relevanten Tabellen, gekürzt um die unnötigen Felder, damit ihr wisst, worum es geht.

//edit: Ich sehe gerade, dass vBulletin das Bild stark verkleinert hat. Hier das Bild in erkennbar.

Vorneweg: Sollte ich uhrzeitbedingt den Wald vor lauter Bäumen nicht gesehen haben, entschuldige ich mich jetzt schon mal

Ich muss dazu etwas ausholen:

Zum Problem:

Jeder User kann einem oder mehreren Bezirken unter Verwendung verschiedener Rollen zugeordnet werden. Außerdem hat jeder User einen Heimatbezirk. Jede Rolle hat wiederum verschiedene Rechte. Die Rolle-Rechte Zuordnung ist in der View "roles_and_rights" zusammengefasst.

In der Tabelle district befindet sich eine Nested-Set Struktur, welche die hierarchisch aufgebauten Bezirke listet. Als Beispiel einfach mal Bundesländer:

Deutschland -> Südwest -> (Hessen, Rheinland, Saarland).

Ich benötige nun ein Query, welcher mir prinzipiell alle User zurück gibt, die sich mit einer bestimmten Rolle in einem der Bezirke befinden, denen der Aktuelle User mit dieser gleichen Rolle zugeordnet ist.

Dabei müssen aber einige Dinge beachtet werden:

- Die User, die zurückgegeben werden, sollen ihren jeweiligen Heimbezirken zugeordnet werden, sofern der aktuelle User, der die Abfrage stellt, in diesen Heimatbezirken die oben angesprochene Rolle hat (Kriterium A)
- Ist dies nicht der Fall, sollen die dem Bezirk zugeordnet werden, wo beide User die Rolle haben (Kriterium B).
- Ist keins der beiden Kriterien erfüllt, soll der User entsprechend nicht auftauchen. Erfüllen mehrere Bezirke das Kriterium 2, so ist der Bezirk niedrigeren Levels zu wählen.

Wichtig ist noch: Einem User ist ein Bezirk nicht zwangsweise explizit zugeordnet. Ist der Boolean recursive in user_district gesetzt, wird die Rollen-Zuordnung entsprechend der Nested-Set Struktur nach unten vererbt.

Die View district_by_right stellt allerdings eine Zusammenfassung (basicly user, district, role, right) dar, welche die Vererbung bereits berücksichtigt.

Ein Beispiel hilft eventuell, das etwas besser zu verstehen:

User A hat Rolle1 in den Bezirken Südwest und Hessen (Heimatbezirk Hessen)
User B hat Rolle1 im Bezirk Hessen (Heimatbezirk Hessen)

User C hat Rolle1 in den Bezirken Südwest und Rheinland (Heimatbezirk Rheinland)

Führt nun User A die Query aus, soll er folgendes Ergebnis bekommen:

Code:

User       |     Bezirk     |     Beschreibung
___________|________________|________________________________________________________________
User A     |     Hessen     | User A besitzt Rolle1 auf User A's Heimatbezirk (Kriterium 1)
User B     |     Hessen     | "
User C     |     Südwest    | User A besitzt NICHT Rolle1 auf User Cs Heimatbezirk, aber gemeinsam Rolle1 im Bezirk Südwest (Krit 2)

Ich erwarte natürlich kein fix und fertiges Query. Über einen Denkanstoß würde ich mich aber freuen

Puh, dann danke ich euch schon mal im Voraus und verabschiede mich für heute ins Bett.

Gruß,
Daniel

Daniel · 24.12.2011, 11:17

Nach weiterem Überlegen bin ich denke ich etwas weitergekommen, habe jedoch immer noch ein Problem.

Gleich die Frage vorweg: Ist es möglich, aus einem Set von Datensätzen denjenigen Datensatz mit dem maximalen Wert in einer Spalte zu geben? Anders formuliert: Gib mir für jeden User denjenigen Datensatz, wo eine andere Spalte maximal wird?

Ich habe mir überlegt, dass ich für das Problem eigentlich nur alle Bezirke brauche, wo beide User das genannte Recht haben, und dann einfach denjenigen Datensatz nehme, wo `district`.`level` am höchsten ist. Das Garantiert im Zweifel den Heimatbezirk eines Users, oder, falls nicht, die niedrigste, gemeinsame Stelle.

Im Endeffekt brauche ich also eine Möglichlichkeit, ORDER BY vor GROUP BY auszuführen. Der folgende Query löst (fast) mein Problem:

Code:

SELECT `dbr1`.`user`, `u`.`name`, `d`.`name` AS `dname`, MAX(`d`.`level`) FROM `dev_district_by_right` AS `dbr1`
INNER JOIN `dev_district_by_right` AS `dbr2` ON `dbr1`.`rightname` = 'Referee' AND `dbr2`.`rightname` = 'Referee' AND `dbr1`.`id` = `dbr2`.`id` AND `dbr2`.`user` = 192
INNER JOIN `dev_user` AS `u` ON `dbr1`.`user` = `u`.`id`
INNER JOIN `dev_district` AS `d` ON `dbr1`.`id` = `d`.`id`
GROUP BY `dbr1`.`user`;

Problem ist, er gibt natürlich das höchste Level aus, nicht jedoch den dazu gehörigen Datensatz. Habt ihr eine Idee dazu?

Als einziges würde mir hier eine Sub-Query einfallen, wo man innerhalb sortiert und außerhalb gruppiert. Aber da gibt es sicherlich elegantere Lösungen

//Edit: Mit SubQueries funktioniert das Ganze. Ich lasse den Thread nochmal über Weihnachten "unerledigt", falls jemand Lust hat, über eine bessere Lösung nachzudenken, das würde mich sehr freuen.
Falls Euch oder mir selbst bis dahin keine andere Lösung einfällt, kommt ein TODO dran und wird nach hinten verschoben.

Frohe Weihnachten Euch!

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
User soll Unterseite erstellen können!?	chickenburner	PHP Einsteiger	9	01.04.2012 17:00
Mit PHP eine Tabelle erstellen	JamieFraser	PHP Einsteiger	6	10.03.2011 14:40
[Erledigt] Mysql - Query dynamisch erstellen - String zusammenfügen	zoom	PHP Tipps 2010	12	19.10.2010 13:50
Websiteteile vom User erstellen lassen	magni	PHP Tipps 2009	4	15.12.2009 11:17
Kleinen PHP Script "Bannerrotation" aus MySQL-Datenbank erstellen...	ispvip	PHP Tipps 2009	2	03.11.2009 09:01
PHP Ordner erstellen mit bestimmten Namen	matzee	PHP Tipps 2009	13	04.09.2009 11:45
[Erledigt] jpeg erstellen aus textstring erstellen	Psydl	PHP Tipps 2009	16	07.07.2009 18:54
[Erledigt] foreach schleife beim erstellen einer image_map schlägt fehl	litterauspirna	PHP Tipps 2009	8	25.05.2009 13:04
ORDER BY (2 Queries)	DonPatricio	PHP Tipps 2009	6	02.02.2009 01:31
darf keine Datenbank erstellen in phpMyAdmin		Datenbanken	2	20.10.2005 19:32
wie viel queries pro seite	zapate	PHP Tipps 2005-2	10	22.07.2005 23:02
Stammbaum erstellen	Riot	PHP Tipps 2005	17	13.05.2005 16:32
Ordner erstellen und Datei uploaden	Juuro	PHP Tipps 2005	6	20.01.2005 21:18
Ordner per Skript erstellen --> Rechte Problem		PHP Tipps 2004	2	06.07.2004 14:19

22.12.2011, 02:06
Daniel Erfahrener Benutzer Registriert seit: 01.06.2008 Beiträge: 179 PHP-Kenntnisse: Fortgeschritten	[Erledigt] Hilfestellung beim Erstellen eines etwas komplexeren Queries Hallo php.de Gemeinde, ich habe mal wieder ein kleines Problem. Für mein Projekt muss ich ein etwas komplexeren Query erstellen, wo mir im Moment leider total die Idee fehlt. Im Anhang ein Ausschnitt der relevanten Tabellen, gekürzt um die unnötigen Felder, damit ihr wisst, worum es geht. //edit: Ich sehe gerade, dass vBulletin das Bild stark verkleinert hat. Hier das Bild in erkennbar. Vorneweg: Sollte ich uhrzeitbedingt den Wald vor lauter Bäumen nicht gesehen haben, entschuldige ich mich jetzt schon mal Ich muss dazu etwas ausholen: Zum Problem: Jeder User kann einem oder mehreren Bezirken unter Verwendung verschiedener Rollen zugeordnet werden. Außerdem hat jeder User einen Heimatbezirk. Jede Rolle hat wiederum verschiedene Rechte. Die Rolle-Rechte Zuordnung ist in der View "roles_and_rights" zusammengefasst. In der Tabelle district befindet sich eine Nested-Set Struktur, welche die hierarchisch aufgebauten Bezirke listet. Als Beispiel einfach mal Bundesländer: Deutschland -> Südwest -> (Hessen, Rheinland, Saarland). Ich benötige nun ein Query, welcher mir prinzipiell alle User zurück gibt, die sich mit einer bestimmten Rolle in einem der Bezirke befinden, denen der Aktuelle User mit dieser gleichen Rolle zugeordnet ist. Dabei müssen aber einige Dinge beachtet werden: - Die User, die zurückgegeben werden, sollen ihren jeweiligen Heimbezirken zugeordnet werden, sofern der aktuelle User, der die Abfrage stellt, in diesen Heimatbezirken die oben angesprochene Rolle hat (Kriterium A) - Ist dies nicht der Fall, sollen die dem Bezirk zugeordnet werden, wo beide User die Rolle haben (Kriterium B). - Ist keins der beiden Kriterien erfüllt, soll der User entsprechend nicht auftauchen. Erfüllen mehrere Bezirke das Kriterium 2, so ist der Bezirk niedrigeren Levels zu wählen. Wichtig ist noch: Einem User ist ein Bezirk nicht zwangsweise explizit zugeordnet. Ist der Boolean recursive in user_district gesetzt, wird die Rollen-Zuordnung entsprechend der Nested-Set Struktur nach unten vererbt. Die View district_by_right stellt allerdings eine Zusammenfassung (basicly user, district, role, right) dar, welche die Vererbung bereits berücksichtigt. Ein Beispiel hilft eventuell, das etwas besser zu verstehen: User A hat Rolle1 in den Bezirken Südwest und Hessen (Heimatbezirk Hessen) User B hat Rolle1 im Bezirk Hessen (Heimatbezirk Hessen) User C hat Rolle1 in den Bezirken Südwest und Rheinland (Heimatbezirk Rheinland) Führt nun User A die Query aus, soll er folgendes Ergebnis bekommen: Code: User \| Bezirk \| Beschreibung ___________\|________________\|________________________________________________________________ User A \| Hessen \| User A besitzt Rolle1 auf User A's Heimatbezirk (Kriterium 1) User B \| Hessen \| " User C \| Südwest \| User A besitzt NICHT Rolle1 auf User Cs Heimatbezirk, aber gemeinsam Rolle1 im Bezirk Südwest (Krit 2) Ich erwarte natürlich kein fix und fertiges Query. Über einen Denkanstoß würde ich mich aber freuen Puh, dann danke ich euch schon mal im Voraus und verabschiede mich für heute ins Bett. Gruß, Daniel Miniaturansicht angehängter Grafiken __________________ Je mehr ich lerne, desto mehr wird mir bewusst, dass ich eigentlich nichts weiß.


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

24.12.2011, 11:17
Daniel Erfahrener Benutzer Registriert seit: 01.06.2008 Beiträge: 179 PHP-Kenntnisse: Fortgeschritten	Nach weiterem Überlegen bin ich denke ich etwas weitergekommen, habe jedoch immer noch ein Problem. Gleich die Frage vorweg: Ist es möglich, aus einem Set von Datensätzen denjenigen Datensatz mit dem maximalen Wert in einer Spalte zu geben? Anders formuliert: Gib mir für jeden User denjenigen Datensatz, wo eine andere Spalte maximal wird? Ich habe mir überlegt, dass ich für das Problem eigentlich nur alle Bezirke brauche, wo beide User das genannte Recht haben, und dann einfach denjenigen Datensatz nehme, wo `district`.`level` am höchsten ist. Das Garantiert im Zweifel den Heimatbezirk eines Users, oder, falls nicht, die niedrigste, gemeinsame Stelle. Im Endeffekt brauche ich also eine Möglichlichkeit, ORDER BY vor GROUP BY auszuführen. Der folgende Query löst (fast) mein Problem: Code: SELECT `dbr1`.`user`, `u`.`name`, `d`.`name` AS `dname`, MAX(`d`.`level`) FROM `dev_district_by_right` AS `dbr1` INNER JOIN `dev_district_by_right` AS `dbr2` ON `dbr1`.`rightname` = 'Referee' AND `dbr2`.`rightname` = 'Referee' AND `dbr1`.`id` = `dbr2`.`id` AND `dbr2`.`user` = 192 INNER JOIN `dev_user` AS `u` ON `dbr1`.`user` = `u`.`id` INNER JOIN `dev_district` AS `d` ON `dbr1`.`id` = `d`.`id` GROUP BY `dbr1`.`user`; Problem ist, er gibt natürlich das höchste Level aus, nicht jedoch den dazu gehörigen Datensatz. Habt ihr eine Idee dazu? Als einziges würde mir hier eine Sub-Query einfallen, wo man innerhalb sortiert und außerhalb gruppiert. Aber da gibt es sicherlich elegantere Lösungen //Edit: Mit SubQueries funktioniert das Ganze. Ich lasse den Thread nochmal über Weihnachten "unerledigt", falls jemand Lust hat, über eine bessere Lösung nachzudenken, das würde mich sehr freuen. Falls Euch oder mir selbst bis dahin keine andere Lösung einfällt, kommt ein TODO dran und wird nach hinten verschoben. Frohe Weihnachten Euch! __________________ Je mehr ich lerne, desto mehr wird mir bewusst, dass ich eigentlich nichts weiß. Geändert von Daniel (24.12.2011 um 12:35 Uhr).