Keleth
09-24-2009, 03:19 PM
Hey guys, I was hoping someone could help me figure out how a particular query I'm running is working.
I have 3 tables in question forums_permissions (permissionID, forumID, general, groupID, roleID, userID, read, write, moderate), forums_groupMemberships (userID, groupID), and forums_roleMemberships (userID, roleID). The goal of the query is to sum all of a users permissions (sum of the read, sum of the write, etc) for each forum and the max of all groups a user might be part of.
SELECT permissions.forumID, SUM(permissions.`read`) AS "read", SUM(permissions.`write`) AS "write", SUM(permissions.moderate) AS moderate
FROM forums_permissions AS permissions, forums_groupMemberships AS groupMemberships, forums_roleMemberships AS roleMemberships
WHERE permissions.general = 1 ||
permissions.userID = 1 ||
(groupMemberships.userID = 1 && groupMemberships.groupID = permissions.groupID) ||
(roleMemberships.userID = 1 && roleMemberships.roleID = permissions.roleID)
GROUP BY permissions.forumID
I know there is no code to get the max of any groups the user is part of, partly because I'm not sure how to do it. But the query isn't working right anyway.
Currently, roleMemberships is empty. The other tables have content relevant to user 1, but when I run the query, I get back 0 rows. If I remove any mention of roleMemberships (the table in FROM and the segment in the WHERE), I get back the right result. If I put it back in then fill junk data into roleMemberships (completely unrelated to user 1), it works as expected.
I can't figure out what's goin on, and I hope someone can help me out.
I have 3 tables in question forums_permissions (permissionID, forumID, general, groupID, roleID, userID, read, write, moderate), forums_groupMemberships (userID, groupID), and forums_roleMemberships (userID, roleID). The goal of the query is to sum all of a users permissions (sum of the read, sum of the write, etc) for each forum and the max of all groups a user might be part of.
SELECT permissions.forumID, SUM(permissions.`read`) AS "read", SUM(permissions.`write`) AS "write", SUM(permissions.moderate) AS moderate
FROM forums_permissions AS permissions, forums_groupMemberships AS groupMemberships, forums_roleMemberships AS roleMemberships
WHERE permissions.general = 1 ||
permissions.userID = 1 ||
(groupMemberships.userID = 1 && groupMemberships.groupID = permissions.groupID) ||
(roleMemberships.userID = 1 && roleMemberships.roleID = permissions.roleID)
GROUP BY permissions.forumID
I know there is no code to get the max of any groups the user is part of, partly because I'm not sure how to do it. But the query isn't working right anyway.
Currently, roleMemberships is empty. The other tables have content relevant to user 1, but when I run the query, I get back 0 rows. If I remove any mention of roleMemberships (the table in FROM and the segment in the WHERE), I get back the right result. If I put it back in then fill junk data into roleMemberships (completely unrelated to user 1), it works as expected.
I can't figure out what's goin on, and I hope someone can help me out.