[PHP][MySQL] Uitgebreide Query, forumindex fetchen

[RedHat]

Hallo luitjes

Ik ga maar eens verder met m'n 'leerprojectje'. Nu gaat het puur om een zo efficient mogelijke query. Een query die EN 2 tables fetcht (de categorieén en de fora) EN controleert of de rechten wel helemaal ok zijn. De rechtentabellen zijn onderverdeeld in roles, usergroups, linkedrights.

De tables:

code:

CREATE TABLE `usergroups` (
  `groupID` int(11) NOT NULL auto_increment,
  `groupName` varchar(50) default NULL,
  `groupColor` varchar(8) default NULL,
  `groupLinkedRightID` int(2) default NULL,
  PRIMARY KEY  (`groupID`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

CREATE TABLE `role` (
  `roleID` int(11) NOT NULL auto_increment,
  `roleCanView` int(1) default NULL,
  `roleCanEdit` int(1) default NULL,
  `roleCanModify` int(1) default NULL,
  `roleCanViewTopicAdmin` int(1) default NULL,
  `rolecanModifyUsers` int(1) default NULL,
  `roleCanModifyUserNotes` int(1) default NULL,
  `roleCanAddUserNotes` int(1) default NULL,
  PRIMARY KEY  (`roleID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;


CREATE TABLE `linkedrights` (
  `linkedID` int(11) NOT NULL auto_increment,
  `linkedGroupID` int(11) default NULL,
  `linkedForumID` int(2) default NULL,
  `linkedRoleID` int(2) default NULL,
  PRIMARY KEY  (`linkedID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

in de tabel linkedrights worden dus alle gegevens in 1 tabel gezet.
dus GroupID heeft in ForumID dat roleID (rechtenset)... Die role moet gecontroleerd worden op die canView, als dat op 1 staat, dan mag hij het forum laten zien op de forumindex, anders niet.

Maar, er hoeven natuurlijk niet altijd rechten gecontroleerd te worden, want een forum kan ook "openbaar" zijn. Als forumIsPrivate = 0 dan is het openbaar, als het 1 is, dan is het een "prive" forum en dan moeten er rechten gecontroleerd worden....

Ik heb een groupID 1, de standaard, met 0 role entry's (dus geen rechtensets) en 0 linkedrights entrys (dus ook in geen 1 forum rechten).

In een cookie word er een userID en een sessionID opgeslagen. Nu denk ik dat het het makkelijkst is om met 2 query's te werken, één als iemand is ingelogt, één als iemand _NIET_ is ingelogt. Dan houd je voor de server in princiepe nog gewoon 1 query over. Dat word dus een totaal van twéé querys (1tje om te controleren of de cookies inderdaad valid zijn en dan 1tje om alle rechten+forums te fetchen)

Okeej, nu dan de query die ik tot nu toe heb:

PHP:

$mainQuery = "
SELECT
  categorys.catID   AS cat_id,
  categorys.catName AS cat_name,
  forums.forumID    AS forum_id,
  forums.forumName  AS forum_name,
  forums.forumDesc  AS forum_desc
FROM       forums
INNER JOIN categorys
         ON categorys.catID = forums.forumCatID
INNER JOIN linkedrights
         ON linkedrights.linkedForumID = forums.forumID
INNER JOIN role
         ON role.roleID = linkedrights.linkedRoleID
WHERE  (forums.forumIsPrivate = 0)
    OR (forums.forumIsPrivate = 1
   AND  role.roleCanView = 1
   AND  linkedrights.linkedGroupID = " .$group ." )
ORDER BY
            categorys.catOrder,
            forums.forumOrder
";

($group heb ik de waarde "1" meegegeven)

Hééft iemand nog tips voor de query? Nu geeft hij 0,0 output (de query). Zijn er nog dingen die ik " in mind " moet houden? Toch vreemd van die 0,0 output, dat MOET in de joins zitten want in de where clausule word al gezegt forums.forumIsPrivate=0 dan moet hij die al als output geven...

Hoop dat de specialisten kunnen kijken naar de query en mij kunnen helpen om te optimaliseren

Het probleem !

Er _MOET_ een linkedright entry zijn, voor die usergroup, anders laat hij hem niet zien... Hij snapt niet echt dat het _ALLEEN_ voor forums zijn die een isPrivate flag hebben die op 1 staat.

Het lijkt erop dat de string $group elke waarde kan zijn maar dat de results hetzelfde blijven. Hij laat alleen het forum zien die ik ff snel in linkedrights heb gezet met een roleid waaar alle rechten op 1 staan...

De hamvraag:

Is dit een goede manier van een rechtenstructuren bouwen, zonee, betere ideën? Zoja, hoop ik dat iemand me kan helpen met optimaliseren en de query daadwerkelijk werkend krijgen

[ Voor 47% gewijzigd door RedHat op 31-03-2005 22:21 ]

[RedHat]

Ik heb de query aangepast, iets versimpeld, met dezelfde werking als het goed is

PHP:

$group = "2";
// QUERY FOR CATEGORYS AND FORUMS
$query = "  
SELECT
  categorys.catID   AS cat_id,
  categorys.catName AS cat_name,
  forums.forumID    AS forum_id,
  forums.forumName  AS forum_name,
  forums.forumDesc  AS forum_desc

FROM       forums

INNER JOIN categorys
         ON categorys.catID = forums.forumCatID
INNER JOIN roles
         ON roles.roleID = permissions.permRoleID
INNER JOIN permissions
         ON permissions.permForumID = forums.forumID

WHERE  
    forums.forumIsPrivate = '0'
   OR (
   roles.roleCanView = 1
   AND  permissions.permGroupID = " .$group ." 
   )
ORDER BY
            categorys.catOrder,
            forums.forumOrder
        ";

resultaat: de forums met forumIsPrivate=0 laat hij nog steeds niet zien.

[RedHat]

Een dag later, toch nog een kickje, want ik ben er nog steeds niet uit en hoop toch een beetje op hulp Probleem is hetzelfde.....

Hij laat alleen de groupen zien met isprivate=1 waar je rechten hebt.
maar de isprivate=0 forums laat hij echter niet zien.

[ Voor 22% gewijzigd door RedHat op 02-04-2005 09:12 ]

[crisp]

Er _MOET_ een linkedright entry zijn, voor die usergroup, anders laat hij hem niet zien... Hij snapt niet echt dat het _ALLEEN_ voor forums zijn die een isPrivate flag hebben die op 1 staat.

En dus zal je een LEFT JOIN moeten gebruiken voor linkedrights (of permissions in je laatste voorbeeld)

[RedHat]

hmmm dat is waar... Dat is een fout, maar niet de fout waar hij op blijft haken. Leuk leerprojectje dit
De query:

code:

SELECT
  categorys.catID   AS cat_id,
  categorys.catName AS cat_name,
  forums.forumID    AS forum_id,
  forums.forumName  AS forum_name,
  forums.forumDesc  AS forum_desc,
  forums.forumIsPrivate AS forum_private

FROM       forums

INNER JOIN categorys
         ON categorys.catID = forums.forumCatID
INNER JOIN roles
         ON permissions.permRoleID = roles.roleID
LEFT JOIN permissions
         ON permissions.permForumID = forums.forumID

WHERE  
    forums.forumIsPrivate =  0
 OR (
  roles.roleCanView = 1
 AND  permissions.permGroupID = " .$group ." 
 )

ORDER BY
            categorys.catOrder,
            forums.forumOrder

de Query moet dus een aantal zaken controlen....

de user die ingelogt is in welke groep hij bevind (dit heb ik even uit de query gelatenom het te versimpelen) okeej dan heb je een groupID.
Die groepID word geselecteerd in de permission table, en alle forums+roles die daarin aangesloten zijn pakt ie dus mee... Die forumID's en RoleID's geven bepaalde rechten (role is puur canview etc)
Dan kun je zien of iemand een forum mag betreden, of niet. Maar als IsPrivate op 0 staat is het een publiek forum en mag iedereen erin lezen/posten/schrijven

die krijgt dus de group 2. Group 2 is geregistreerde gebruikers. Zo kun je in vervolg de "niet private foras" ook heel makkelijk veranderen.

[ Voor 3% gewijzigd door RedHat op 03-04-2005 10:42 ]

[crisp]

Misschien moet je gewoon niet proberen alles in 1 query te doen
Ik denk dat als je het uitsplitst je veel eenvoudigere queries overhoud die samen waarschijnlijk nog wel sneller uit te voeren zijn dan 1 hele ingewikkelde.

[RedHat]

Hoe stelt u zich dat voor?
1 Query waar de usergegevens uitgehaald worden.
1 Query voor de forums+categorieën ?

de forumid's met canview=1 in een array gooien en dan met if (in_array) toegang(); (oid, werk eigenlijk nooit met arrays ) Toch maar voor het KISS-principe

Ik kan me eigenlijk geen functionele split-query bedenken. Alles heeft zo z'n voor & nadelen. Zolang hij maar alle gegevens in 1x ophaalt (dus als je het splits 2x in 1x) dan ben ik al zeer tevreden. Heb wel n's scriptjes gezien die in een while-lus een query hadden staan...

Om het ff duidelijk te stellen, ik kan me ff geen bedenking maken waar ik de "link" tussen die 2 query's moet leggen. Dus welke query haalt wat op en waar leg ik de link tussen die 2 query's om te controleren wie wat mag

[ Voor 100% gewijzigd door RedHat op 03-04-2005 12:58 ]

[crisp]

Ja zoiets, eventueel kan je een niet-ingelogde gebruiker ook een role toekennen.

[RedHat]

crisp schreef op zondag 03 april 2005 @ 12:57:
Ja zoiets, eventueel kan je een niet-ingelogde gebruiker ook een role toekennen.

Dat is al zo... Usergroup 1 is Niet-geregistreerd, Usergroup2 is Geregistreerd, en dan kun je nog "speciale" groepen aanmaken. Zo kun je in de toekomst simpel de rechten veranderen zonder de code steeds te veranderen oid.

Zou je misschien een paar functies kunnen opnoemen die van pas zouden kunnen komen? Ik ken natuurlijk niet alle functies uit mijn hoofd en zo'n pro ben ik niet dus lijkt me wel leerzaam
ik denk dat ik gewoon een array maak met forumID's. alle forumID's die met in_array(); gevonden worden krijgen doorgang, de rest niet. Is dit "de" manier?

Hartelijk dank crisp! Kan weer lekker aan de gang

[ Voor 47% gewijzigd door RedHat op 03-04-2005 13:23 ]

[crisp]

code:

SELECT
    categorys.catID   AS cat_id,
    categorys.catName AS cat_name,
    forums.forumID    AS forum_id,
    forums.forumName  AS forum_name,
    forums.forumDesc  AS forum_desc,
    forums.forumIsPrivate AS forum_private
FROM
    forums
INNER JOIN categorys
    ON categorys.catID = forums.forumCatID
LEFT JOIN permissions
    ON (
        permissions.permForumID = forums.forumID
        AND permissions.permGroupID = " . $group . "
    ) 
LEFT JOIN roles
    ON permissions.permRoleID = roles.roleID
WHERE  
    forums.forumIsPrivate =  0
OR
    roles.roleCanView = 1
ORDER BY
    categorys.catOrder,
    forums.forumOrder

en zoiets?

of zelfs zo:

code:

SELECT
    categorys.catID   AS cat_id,
    categorys.catName AS cat_name,
    forums.forumID    AS forum_id,
    forums.forumName  AS forum_name,
    forums.forumDesc  AS forum_desc,
    forums.forumIsPrivate AS forum_private
FROM
    forums
INNER JOIN categorys
    ON categorys.catID = forums.forumCatID
LEFT JOIN (
        permissions
        INNER JOIN roles
            ON permissions.permRoleID = roles.roleID
    ) ON (
        permissions.permForumID = forums.forumID
        AND permissions.permGroupID = " . $group . "
    )
WHERE  
    forums.forumIsPrivate =  0
OR
    roles.roleCanView = 1
ORDER BY
    categorys.catOrder,
    forums.forumOrder

[ Voor 35% gewijzigd door crisp op 03-04-2005 13:36 ]

[RedHat]

nee dat gaat niet....
als er nu geen record is voor een forum dat isprivate op 1 heeft steen, laat hij helemaal niets zien, is er wel een record dan laat ie alles zien.

dus: GEEN record waar roleCanView=1 >> output = 0 cats+forums
dus: WEL record waar roleCanView=1 >> output goed, alle cats+forums + private forum.

de bovenste query zit technisch gezien goed in elkaar...
en praktisch gezien ook. Die werkt wel. Bij de onderste (ff snel gekeken) denk ik dat het fout gaat omdat er een table 2x gejoined word. (volgens mij)

Denk dat ik het maar bij dit houd.
En dan nog een query voor usergegevens (gewoon in een if, als ingelogt & door inlog-controle heen, dan dynamische gegevens uit db gebruiken voor toegang of niet, als niet is ingelogt statische gegevens gebruiken (dwz usergroup1 =nietingelogt als rechtenset gebruiken).

[ Voor 55% gewijzigd door RedHat op 03-04-2005 14:21 ]