List:General Discussion« Previous MessageNext Message »
From:Chris W Date:April 7 2010 3:05am
Subject:Combine Two Queries
View as plain text  
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
Thread
Combine Two QueriesChris W7 Apr