[Basti]

Hallo.

Ich hänge mal wieder an einer MySQL-Abfrage (Version 5.0.*). Mpsste eigentlich ganz einfach gehen - denke ich - komm aber grad nicht dahinter:

Zwei Tabellen:

roles
id, user, group, role

derived_roles
id, derived_from_group, deived_from_role, group, role

In einer Tabelle werden also die Rollen eines Benutzers definiert (Rolle bezieht sich immer auf eine Gruppe). In einer zweiten Tabelle werden Regeln definiert, Bei welcher Rolle in welcher Gruppe ein Benutzer elche weitere Rolle in welcher Gruppe erbt.

Beispiel:
Benutzer A arbeitet in Firma X, welche durch die Gruppe grp_x dargestellt wird. Nun bietet Firma X auch die Leistung Y an. Alle Anbieter dieser Leistung sind in Gruppe grp_y versammelt. Die Regel ist also:

Alle Benutzer mit der Rolle member (oder so) der Gruppe grp_x, erben die Rolle member der Gruppe grp_y:

roles:
------
id:    1
user:  paul
group: grp_x
role:  member

derived_roles:
--------------
id:
derived_from_group: grp_x
derived_from_role:  member
group:              grp_y
role:               member

Eine Abfrage soll nun die Rollen eines Benutzers zurückgeben (unabhängig, ob diese abgeleitet sind oder nicht). Hier soll sie also grp_x|member und grp_y|member zurückgeben.

Any approaches?

Basti

[Perspektivisch soll das dann auch für verschiedene Gruppentypen gehen: Wenn ein Benutzer die Rolle Moderator in einer Forengruppe hat (jede Gruppe ist von einem bestimmten Gruppentyp durch den die jeweilige Rollen definiert werden), dann erbt er die Rolle Mitglied in der Gruppe "Moderatorenforum". Aber das ist im Moment noch nicht gefordert.]

PS:
"Gib mir alle Datensätze aus roles, bei denen user = 'paul' und alle Datensätze aus derived_roles, in denen derived_from_group und derived_from_role so als Paar mit user = 'paul' in roles vorkommen."

[$traight-$hoota]

sollte eigentlich relativ einfach sein:

SELECT `group`, `role`
FROM `roles`
WHERE `user` = "paul"
UNION
SELECT `derived_roles`.`group`, `derived_roles`.`role`
FROM `derived_roles`
JOIN `roles` ON `derived_roles`.`derived_from_group` = `roles`.`group` AND `derived_roles`.`derived_from_role` = `roles`.`role`
WHERE `roles`.`user` = "paul"

kompliziert wird es nur, wenn auch abgeleitete rollen abgeleitet werden sollen (sollen sie das??). dann hilft nämlich nurnoch application-seitige rekursion...

[Basti]

Vielen Dank!

Im Moment reicht eine einfache Ableitung.

Basti

[Basti]

Super, das klappt.

...fast (wie könnte es anders sein *g).

Ich hab jetzt für beide Queries ein Feld 0 bzw. 1 AS `derived` hinzugefügt, um die Info zu haben, ob eine Rolle abgeleitet ist oder ob sie explizit erworben wurde.

Gut ist, dass schonmal alle doppelten Einträge verschwinden, aber wenn eine Rolle sowohl explizit erworben (Eintrag in roles), als auch abgeleitet ist, dann erscheint sie jetzt (natürlich) doppelt, da ja beide Datensätze nicht mehr übereinstimmen.

Ich kann so damit arbeiten, feiner wäre es allerdings, wenn solche doppelten Einträge (Rolle ist sowohl explizit vergeben, als auch abgeleitet) z.B. den Wert 2 als Attribut derived erhielten.

Hintergrund ist der, dass man einem Benutzer eine abgeleitete Rolle nicht entziehen kann. Aber man kann ihm eine explizit vergebene Rolle entziehen, auch, wenn er diese Rolle dann noch so lange weiter inne hat, wie er sie von irgendwo her erbt. Andersrum kann man einem Benutzer eben auch eine Rolle explizit verpassen, die er bereits aus einer anderen Rolle her erbt.

Meine Query jetzt:

SELECT
    `group`,
    `role`,
    0 AS `derived`

FROM
    `roles`

WHERE
    `user` = 'alex'

UNION
    SELECT
        `derived_roles`.`group`,
        `derived_roles`.`role`,
        1 AS `derived`

    FROM
        `derived_roles`

    JOIN
        `roles`
    ON
        `derived_roles`.`derived_from_group` = `roles`.`group`
    AND
        `derived_roles`.`derived_from_role` = `roles`.`role`
    WHERE
        `roles`.`user` = 'alex'

Testdaten:

CREATE TABLE `derived_roles` (
  `id` int(11) NOT NULL auto_increment,
  `derived_from_group` varchar(255) NOT NULL,
  `derived_from_role` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `derived_roles` (`id`, `derived_from_group`, `derived_from_role`, `group`, `role`) VALUES (1, 'grp1', 'admin', 'grp2', 'member');
INSERT INTO `derived_roles` (`id`, `derived_from_group`, `derived_from_role`, `group`, `role`) VALUES (2, 'grp1', 'admin', 'grp3', 'member');
INSERT INTO `derived_roles` (`id`, `derived_from_group`, `derived_from_role`, `group`, `role`) VALUES (3, 'grp3', 'admin', 'grp2', 'member');

CREATE TABLE `roles` (
  `id` int(11) NOT NULL auto_increment,
  `user` varchar(255) NOT NULL,
  `group` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (1, 'basti', 'grp1', 'admin');
INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (2, 'basti', 'grp2', 'moderator');
INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (3, 'paul', 'grp3', 'member');
INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (4, 'alex', 'grp1', 'admin');
INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (5, 'alex', 'grp2', 'member');
INSERT INTO `roles` (`id`, `user`, `group`, `role`) VALUES (6, 'alex', 'grp3', 'admin');

Basti

[$traight-$hoota]

das wäre durch ein subquery realisierbar, is aber aufwendig und bringt nix. application-seitige überprüfung ist da mit sicherheit performanter.

[Basti]

Hi.

Nach einiger Zeit, widme ich mich wieder diesem Punkt hier und komm grad wieder nicht weiter.

Problem ist, dass bei der Abfrage nicht nur Gruppe und Rolle ausgelesen werden müssen, sondern auch der Name der Gruppe und der Rolle und der Gruppentyp.

Die Abfrage ohne die abgeleiteten Rollen sieht so aus:

PHP-Code:


            SELECT
                `roles`.`id`             AS `id`,
                `roles`.`role`           AS `role`,
                `grouptype_roles`.`name` AS `role_name`,
                `groups`.`id`            AS `group_id`,
                `groups`.`name`          AS `group_name`,
                `groups`.`grouptype`     AS `grouptype`,
                `grouptypes`.`name`      AS `grouptype_name`

            FROM
                `roles`,
                `grouptype_roles`,
                `groups`,
                `grouptypes`

            WHERE
                `roles`.`user` = '%s'
            AND
                `roles`.`group` = `groups`.`id`
            AND
                `grouptype_roles`.`role` = `roles`.`role`
            AND
                `grouptype_roles`.`grouptype` = `groups`.`grouptype`
            AND
                `groups`.`grouptype` = `grouptypes`.`id`

            ORDER BY
                `grouptypes`.`order`,
                `group_name`,
                `grouptype_roles`.`order` 


Die ID muss natürlich raus, da abgeleitete Rollen (erstmal) keine ID haben.

Wenn ich nun die zweite Abfrage darein zu friemeln Versuche, bekomme ich bei jedem Ansatz, der mir so einfällt Fehler.

Sobald ich z.B. die Spalte `grouptype_roles`.`role` in der zweiten Query mit auslesen möchte, meckert MySQL, dass er roles_derived.derived_from_group nicht kennt, obwohl er damit vor dieser Eweierung keine Probleme hatte.

Hat jemand da einen Ansatz?

Hier mal die Query - noch ganz ohne Verknüpfung mit grouptype_roles.

PHP-Code:

SELECT
    `group`,
    `roles`.`role`,
    `grouptype_roles`.`name` AS `role_name`,
    0 AS `derived`

FROM
    `roles`,
    `grouptype_roles`

WHERE
    `user` = '2'

UNION
    SELECT
        `roles_derived`.`group`,
        `roles_derived`.`role`,
        `grouptype_roles`.`name` AS `role_name`,
        1 AS `derived`

    FROM
        `roles_derived`,
        `grouptype_roles`

    JOIN
        `roles`
    ON
        `roles_derived`.`derived_from_group` = `roles`.`group`
    AND
        `roles_derived`.`derived_from_role` = `roles`.`role`
    WHERE
        `roles`.`user` = '2' 


Fehler:

#1054 - Unknown column 'roles_derived.derived_from_group' in 'on clause'

Basti

[Basti]

...habs mit einer Subquery hinbekommen:

SELECT
	`users_roles`.`role`     AS `role`,
	`users_roles`.`derived`  AS `derived`,
	`grouptype_roles`.`name` AS `role_name`,
	`groups`.`id`            AS `group_id`,
	`groups`.`name`          AS `group_name`,
	`groups`.`grouptype`     AS `grouptype`,
	`grouptypes`.`name`      AS `grouptype_name`

FROM
	`groups`,
	`grouptype_roles`, 
	`grouptypes`,
	(SELECT
		`group`,
		`roles`.`role`,
		0 AS `derived`

	FROM
		`roles`,
		`grouptype_roles`

	WHERE
		`user` = '%s'

	UNION
		SELECT
			`roles_derived`.`group`,
			`roles_derived`.`role`,
			1 AS `derived`

		FROM
			`roles_derived`

		JOIN
			`roles`
		ON
			`roles_derived`.`derived_from_group` = `roles`.`group`
		AND
			`roles_derived`.`derived_from_role` = `roles`.`role`
		WHERE
			`roles`.`user` = '%s')

	AS `users_roles`

WHERE
	`users_roles`.`group` = `groups`.`id`
AND
	`groups`.`grouptype` = `grouptype_roles`.`grouptype`
AND
	`groups`.`grouptype` = `grouptypes`.`id`

ORDER BY
	`derived`,
	`grouptype`,
	`group_name`,
	`group_id`,
	`role_name`

Basti

[Basti]

Mist stimmt wieder was nicht! Die Rollen-Namen werden nicht korrekt ausgelesen!

...Monolog eines Genervten!

Basti

PS:
Das hier fehlte noch:

AND
	`grouptype_roles`.`role` = `users_roles`.`role`

Witzig, wenn man selbst schon nicht mehr durchblickt, einfach mal macht und es dann auch noch funktioniert - zumindest bis zum nächsten Fehler halt...