I have a content management system. One of the methods I use to grant
access to various parts of the site is with Groups. I can link Pages,
Users, Modules, etc (objects) to any number of groups. So a Many to
Many relationship. I use the grouplink table to do this.
CREATE TABLE `grouplink` (
`LinkType`
set('user','page','template','templatefile','menu','module') NOT NULL
DEFAULT '',
`ID` int(10) unsigned NOT NULL DEFAULT '0',
`GroupID` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`LinkType`,`ID`,`GroupID`)
) ;
LinkType indicates what type of object I am linking to. If I am
linking to a page, ID is the PageID, if to a User, ID is UserID...
etc. And GroupID is just the group I am linking the object to.
The group table looks like this...
CREATE TABLE `group` (
`GroupID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`GroupName` varchar(45) NOT NULL DEFAULT '',
`Active` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`GroupID`)
);
My rule to give a user access to any object is the user has to be linked
to at least one of the same groups that object is linked to. There is
one exception to that rule, and that is, if an object isn't linked to
any groups then it doesn't matter what groups the User is in. Currently
I use two queries to implement these rules. If the Count on the first
query is 0, they access is granted, if not I execute the second query
and if the count on it is greater than 0, access is granted.
SELECT COUNT(`GroupID`)
FROM `grouplink`
WHERE `LinkType` = '$LinkType' AND `ID` = '$ID'
SELECT COUNT (g.`GroupID`)
FROM `grouplink` u //Groups the user, UserID is in
JOIN `grouplink` l USING (`GroupID`) //Groups the LinkType, ID is in
JOIN `group` g USING (`GroupID`)
WHERE u.`LinkType` = 'user' AND l.`LinkType` = '$LinkType'
AND u.`ID` = '$UserID' AND l.`ID` = '$ID'
AND g.`Active`
Is there any way merge these into one query?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
