[MySQL] query die twee tabellen matched, records uit andere

[GWTommy]

Best moeilijk om dit in de titel te omschrijven zo kort..
Ik heb moeite met een *lastige* (voor mij dan) query in MySQL. Het zit zo.

Ik heb de volgende tabellen:

code:

+ Users
- id
- name
- group
...

+ Groups
- id
- name
...

+ Permissions
- id
- name
...

+ PermissionToGroup
- id
- groupId
- permissionId
- mode (enum allow/deny)

+ PermissionToUser
- id
- userId
- permissionId
- mode (enum allow/deny)

Het idee is dus het volgende:
Een query die selecteerd aan de hand van een userId (users.id) welke permissions (alle matchende records uit die tabel gewoon selecteren) die heeft, waarbij deze aan elkaar worden gekoppeld door de tabellen PermissionToGroup en PermissionToUser. Welke groep nodig is kan aan de hand van het userId dat in de query wordt opgegeven uit de tabel users worden gehaald en gebruikt worden om gegevens uit de tabel PermissionsToGroup te trekken. Permissions die specifiek aan de user zijn toegewezen overschijven die aan de group waartoe de user behoort zijn toegewezen. Ik wil dus in m'n resultaten van alle records het id en name waarvan permissionToGroup.mode = allow en permissionToUser.mode = allow (of niet aanwezig) of permissionToGroup.mode = deny (of niet aanwezig) en permissionToUser = allow. PermissionToUser.mode = deny is standaard record wegfilteren. PermissionToUser overschijft dus (wanneer aanwezig) permissionToGroup.

Nou kan ik aardig leuke queries maken voor mijn doen, maar JOIN wil er bij mij nog niet in. Iedere keer als ik wat probeer met een leuke (LEFT/RIGHT/INNER/OUTER/...) JOIN dan gaat het fout.
Een tip wat ik het beste kan doen qua constructie hiervan kan mij eventueel al op weg helpen (ik wil er ook best zelf wat voor nadenken, heb ik al gedaan, maar goed, you get what I mean). Een voorbeeld zou ook mooi zijn, iemand anders moet al ooit met zo'n zelfde constructie hebben gezeten dus een voorbeeld zal er vast ook wel zijn.

Thnx.

[dev10]

Ik zou toch lekker met JOINS werken. Dat werkt het beste. Wat gaat er precies verkeerd met het maken van je query als je JOINS gebruikt?

De volgende code haalt alle permissies op voor een gebruiker. Voor groepen is hetzelfde principe.

SQL:

SELECT Permissions.* FROM PermissionToUser
JOIN Permissions ON PermissionToUser.permissionId = Permissions.id
WHERE Users.id = 1

[Anoniem: 26306]

Ik zou aan de volgende basis denken:

MySQL:

SELECT
   u.id,
   u.name AS username,
   g.name AS groupname,
   COALESCE(up.name, gp.name) AS permname,
   COALESCE(p2u.mode, p2g.mode) AS permmode
FROM
   Users u
   LEFT JOIN Groups g ON u.group = g.id
   LEFT JOIN PermissionsToUser p2u ON p2u.userId = u.id
   LEFT JOIN PermissionsToGroup p2g ON p2g.groupId = g.id
   LEFT JOIN Permissions up ON p2u.permissionId = up.id
   LEFT JOIN Permissions gp ON p2g.permissionId = gp.id

Allemaal left joins omdat je niet weet of een gebruiker bij minstens een groep hoort (denk ik?) en omdat je niet zeker weet of de gebruiker wel specifieke permissies heeft. Zo wordt NULL ingevuld voor alle kolommen uit tabellen waarop geen regels matchen met de join clause. Ik denk dat je coalesce nodig hebt omdat geen van de permissies per se een waarde hoeven hebben. Maar als ze een waarde hebben heeft de waarde van de permissie die aan de user is gekoppeld een hogere prioriteit dan die van de groep.

[GWTommy]

Cheatha, aan de hand van jouw voorbeeld heb ik dit geschreven:

MySQL:

SELECT
    permissions.*
FROM
    permissions
LEFT JOIN
    permissionToGroup
    ON permissionToGroup.permissionId = permissions.id
LEFT JOIN
    groups
    ON groups.id = permissionToGroup.groupId
LEFT JOIN
    users
    ON users.group = groups.id
LEFT JOIN
    permissionToUser p2u
    ON (p2u.permissionId = permissions.id) AND (p2u.userId = users.id)
WHERE users.id = 973 AND COALESCE(p2u.mode, permissionToGroup.mode) = 'allow' AND permissions.denyAll = '0'

Met deze tabelvulling:

code:

INSERT INTO `permissions` (`id`, `name`, `comment`, `denyAll`) VALUES (1, 'test', 'test', '0');
INSERT INTO `permissions` (`id`, `name`, `comment`, `denyAll`) VALUES (2, 'test2', '.', '0');
INSERT INTO `permissions` (`id`, `name`, `comment`, `denyAll`) VALUES (3, 'test3', '.', '0');
INSERT INTO `permissions` (`id`, `name`, `comment`, `denyAll`) VALUES (4, 'test4', '.', '0');

INSERT INTO `permissionToGroup` (`id`, `permissionId`, `groupId`, `mode`) VALUES (1, 1, 1, 'deny');
INSERT INTO `permissionToGroup` (`id`, `permissionId`, `groupId`, `mode`) VALUES (2, 2, 1, 'allow');
INSERT INTO `permissionToGroup` (`id`, `permissionId`, `groupId`, `mode`) VALUES (3, 3, 3, 'allow');
INSERT INTO `permissionToGroup` (`id`, `permissionId`, `groupId`, `mode`) VALUES (4, 3, 1, 'allow');

INSERT INTO `permissionToUser` (`id`, `permissionId`, `userId`, `mode`) VALUES (1, 1, 973, 'allow');
INSERT INTO `permissionToUser` (`id`, `permissionId`, `userId`, `mode`) VALUES (3, 3, 973, 'allow');

Waarbij user.id =973 heeft user.group =1
En als resultaat komen er drie rijen terug met permissions.id = 1,2,3, waar het moet zijn 1,2,3,4.
Overigens maakt het niet uit hoe ik alles instel of wat voor waardes/records ik invoeg, permissions.id=4 komt nooit mee met de results.

Mijn excuses, ik kan deze data/results niet vager naar jullie over brengen, maar toch is het het beste wat ik kan doen. Van die mooie terminal achtige overzichten van records krijg ik met HeidiSQL niet geëxporteerd.
Zo zie je maar weer, je denkt iets redelijk te beheersen en dan krijg je dit nog niet eens voor elkaar. Ik kan het ook met PHP doen en gewoon de inhoud van de tabellen permissions, permissionToGroup en permissionToUser selecteren en dan gaan vergelijken, maar ik weet dat MySQL het sneller kan en het scheelt wat regels code en queries.

Btw, die functie COALESCE() is een goeie, dat is inderdaad wat ik moet hebben voor het allow/deny verhaal.. Thnx

[Anoniem: 26306]

Logisch, je doet niets met permission met id 4. Elke join zal falen omdat in de andere tabellen nergens naar die permission verwezen wordt. Alle waarden behalve die van kolommen uit de tabel "permissions" zijn NULL.
Bekijk het resultaat maar van SELECT * als je geen WHERE clause gebruikt.

users.id = 973 is dus false
COALESCE(p2u.mode, permissionToGroup.mode) is ook false

Je zult dus IS NULL moeten gebruiken als je die rijen ook wilt.

Dus:

MySQL:

SELECT
    permissions.*
FROM
    permissions
LEFT JOIN
    permissionToGroup
    ON permissionToGroup.permissionId = permissions.id
LEFT JOIN
    groups
    ON groups.id = permissionToGroup.groupId
LEFT JOIN
    users
    ON users.group = groups.id
LEFT JOIN
    permissionToUser p2u
    ON (p2u.permissionId = permissions.id) AND (p2u.userId = users.id)
WHERE
   (users.id = 973 OR users.id IS NULL)
   AND
   (COALESCE(p2u.mode, permissionToGroup.mode) = 'allow' OR (p2u.mode IS NULL AND permissionToGroup.mode IS NULL))
   AND
   permissions.denyAll = '0'

[Voor 4% gewijzigd door Anoniem: 26306 op 09-06-2011 22:31]

[GWTommy]

++++1 Voor jou!
Super.

Moet écht aan m'n logica gaan werken haha. Maak té veel denkfouten de laatste tijd. Ofja, ik heb meestal dingen niet goed genoeg doordacht.

Is er overigens nog een betere manier om de dubbele resultaten er uit te halen (die dus in permissionToGroup én permissionToUser een record hebben) behalve DISTINCT? Vind dat namelijk altijd zó lelijk staan.

Bedankt!

[GWTommy]

Daar ben ik weer.
Dit keer wil ik een query die voor iedere groep alle rechten met daarbij allow/deny output.

Ik heb nu dit:

MySQL:

            SELECT
                permissions.id AS permissionId,
                groups.id AS groupId,
                permissions.name,
                permissions.comment,
                IF(permissionToGroup.mode IS NULL, 'deny', permissionToGroup.mode) AS mode,
                permissions.denyAll
            FROM
                groups,
                permissions
            LEFT JOIN
                permissionToGroup
                ON permissionToGroup.permissionId = permissions.id
                AND permissionToGroup.groupId = groupId

Echter krijg ik hiermee voor iedere keer dat een permission in permissionToGroup.permissionId voorkomt iedere groep terug met alle records de zelfde waarde, wat dus niet de bedoeling is.

Ik probeer een overzicht te maken dat er ongeveer zo uit ziet:

code:

+---------+---------+---------+---------+-----
|         | Groep 1 | Groep 2 | Groep 3 | ...
+---------+---------+---------+---------+-----
| Recht 1 |    v    |    v    |    x    | ...
| Recht 2 |    x    |    v    |    x    | ...
| Recht 3 |    x    |    x    |    v    | ...
| Recht 4 |    v    |    x    |    v    | ...
| Recht 5 |    v    |    v    |    x    | ...
| Recht 6 |    x    |    v    |    v    | ...
| ...     |   ...   |   ...   |   ...   | ...

Na een uurtje van alles proberen ben je dus echt ten einde raad!
Ik krijg nou zulke resultaten:

MySQL:

INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (1, 1, 'test', 'test', 'deny', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (1, 2, 'test', 'test', 'deny', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (1, 3, 'test', 'test', 'deny', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 1, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 1, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 2, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 2, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 3, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (2, 3, 'test2', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 1, 'test3', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 1, 'test3', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 2, 'test3', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 2, 'test3', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 3, 'test3', '.', 'allow', '0');
INSERT INTO `permissions` (`permissionId`, `groupId`, `name`, `comment`, `mode`, `denyAll`) VALUES (3, 3, 'test3', '.', 'allow', '0');
...

Sorry voor de insert, maar zo'n overzicht krijg ik nog steeds niet gemaakt (behalve met de hand).

[Precision]

GWTommy schreef op zondag 12 juni 2011 @ 19:02:

Ik probeer een overzicht te maken dat er ongeveer zo uit ziet:

code:

+---------+---------+---------+---------+-----
|         | Groep 1 | Groep 2 | Groep 3 | ...
+---------+---------+---------+---------+-----
| Recht 1 |    v    |    v    |    x    | ...
| Recht 2 |    x    |    v    |    x    | ...
| Recht 3 |    x    |    x    |    v    | ...
| Recht 4 |    v    |    x    |    v    | ...
| Recht 5 |    v    |    v    |    x    | ...
| Recht 6 |    x    |    v    |    v    | ...
| ...     |   ...   |   ...   |   ...   | ...

Volgens mij gaat het hier fout, je wil geen overzicht waarbij je niet weet hoeveel kolommen je hebt. Zoals ik het nu lees kan je 2 groepen hebben maar ook 5. Lees het topic nog eens door.

[GWTommy]

Sorry, ik ben te onduidelijk geweest, ik wil resultaten in deze vorm:
permissionId, groupId, name, comment, mode, denyAll

Wat ik later weer een paar keer foreach zodat ik zo'n overzicht krijg. Excuses.

[GWTommy]

Klein bumpje, ben nog steeds bezig, heb nog niet gevonden hoe ik het nou wel moet doen.

[GWTommy]

Nog een bump?