[MySQL] Query met join

[ytterx]

Ik ben al een tijd bezig een query op te zetten (ook met behulp van google). Deze query moet een tweetal gegevens ophalen, namelijk een 'permKey'(string) en een 'value' (true/false). Dit moet echter gebeuren uit een aantal verschillende database tables.

Ik heb de volgende tabelen:

permissions
ID	int
permKey	string

user_perms
ID	int
userID	int
permID	int
value	bool

role_perms
ID	int
roleID	int
permID	int
value	bool

user_roles
roleID	int
userID	int

Ik wil dus de permKey en de value uit de tabelen, `permissions`, `user_perms` en `role_perms` hebben. waarbij de tabel `user_perms` de overwinnende is. Dat wil zeggen dat als er 2 permKey entry's zijn de gene van de user_perms wint en zijn waarde wordt gebruikt.

tot nu toe heb ik de volgende query:

SQL:

SELECT `permissions`.`permKey`, (`role_perms`.`value` & `user_perms`.`value`) as `value`
FROM `role_perms`
JOIN `permissions`
ON (`permissions`.`ID` = `role_perms`.`permID`)
JOIN user_roles
ON (`user_roles`.`roleID` = `role_perms`.`roleID`)
JOIN user_perms
ON (`user_perms`.`permID` = `permissions`.`ID`)
WHERE `user_roles`.`userID` = `1`

Deze query geeft maar 1 rij terug, namelijk als de `permID` in `role_perms` en in `user_perms` voorkomt. terwijl ik alle `permKey` wil hebben.
(`userID` is gewoon gekozen.. die moet variabel worden.)

[RobIII]

Hoe werken joins? Wat jij wil is geen inner join (die je hier ook nog eens impliciet doet door niet aan te geven of je left/right/... join wil) maar een outer join.

[ Voor 44% gewijzigd door RobIII op 23-11-2010 20:43 ]

[NMe]

Met outer joins en een coalesce komt je een heel eind.

[ytterx]

Uit eindelijk is de sql dit geworden:

SQL:

SELECT `permissions`.`permKey`, COALESCE(`user_perms`.`value`, `role_perms`.`value`) as `value` 
FROM `permissions` 
LEFT JOIN `user_perms`
ON (`user_perms`.`permID` = `permissions`.`ID`) 
JOIN `role_perms` 
ON (`permissions`.`ID` = `role_perms`.`permID`) 
JOIN user_roles 
ON (`user_roles`.`roleID` = `role_perms`.`roleID`)
WHERE `user_roles`.`userID` = 1
GROUP BY `permissions`.`ID`

Bedankt voor de hulp!

[eek]

Misschien ook handig om te weten dat je qua performance beter kan beginnen bij de tabel 'user_roles' en vanaf daar te gaan joinen.

[cariolive23]

eek schreef op dinsdag 23 november 2010 @ 22:32:
Misschien ook handig om te weten dat je qua performance beter kan beginnen bij de tabel 'user_roles' en vanaf daar te gaan joinen.

Waarom dan? Ik heb geen resultaten van EXPLAIN gezien die dit aantonen.

Ik ben wel met je eens dat dit voor de hand ligt, zie de WHERE, maar dat hoeft dus niet. Het ligt er maar net aan hoe de database de query uitvoert en ook welke data er in de database staat. It depends...

Overigens is de GROUP BY `permissions`.`ID` fout, deze moet weg.

[NMe]

Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

[ytterx]

SQL:

SELECT (`permissions`.`permKey`), 
  MAX(COALESCE(`user_perms`.`value`, `role_perms`.`value`)) as `value`
    FROM `user_roles`
        JOIN `role_perms`
            ON (`user_roles`.`roleID` = `role_perms`.`roleID`)
        JOIN permissions
            ON (`permissions`.`ID` = `role_perms`.`permID`)
        LEFT JOIN `user_perms`
            ON (`user_perms`.`permID` = `permissions`.`ID`)
    WHERE `user_roles`.`userID` = 1
    GROUP BY `permissions`.`permKey`

dus als ik het goed heb moet het zo zijn?

[RobIII]

Je Group By klopt nu inderdaad.