I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships.
For simplicity sake, lets say users only contains one field user_id, and groups only contains group_id. relationships uses three fields: user_id, group_id, related_group_id to represent both user to group and group to group relationships.
A dynamically populated group hierarchy would be something like:
Users A, B, C belong to Group 1 and Users D, E, F belong to Group 2. All of these are represented in the relationships table as (user_id,group_id):
A,1
B,1
C,1
D,2
E,2
F,2
The dynamic Group 3 would have two group to group relationship records (group_id,related_group_id):
3,1
3,2
To illustrate my problem better lets add a Dynamic Group 4 and have it include Group 3:
4,3
Piecing together what I've learned of MySQL CURSOR's, I've created the following stored procedure called fetch_inheritance_groups:
DROP PROCEDURE IF EXISTS `fetch_inheritance_groups`;
CREATE PROCEDURE `fetch_inheritance_groups`(IN parent_id INT)
READS SQL DATA
BEGIN
DECLARE inherit_id char(32);
DECLARE eol BOOLEAN;
DECLARE inherit_cur CURSOR FOR SELECT r.Related_Group_ID FROM usr_relationships r WHERE r.Group_ID = parent_id AND r.Related_Group_ID IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET eol = TRUE;
OPEN inherit_cur;
inherited: LOOP
FETCH inherit_cur INTO inherit_id;
IF eol
THEN
CLOSE inherit_cur;
LEAVE inherited;
ELSE
CALL fetch_inheritance_groups(inherit_id);
END IF;
SELECT inherit_id;
END LOOP inherited;
END;
...which is called with...
CALL fetch_inheritance_groups(4);
...and returns the expected results of
3
2
1
Herein begins my problem, they're returned as three separate result sets rather than three rows in a single result set, and secondly, the results are meant to be used within a query like:
SELECT r.uer_id FROM relationships r WHERE r.group_id = 4 OR r.group_id IN (CALL fetch_inheritance_groups(4));
So the three questions I'm hoping someone can answer are:
1. Should I be using a CURSOR here or is there an alternative that will get the same recursive result?
2. How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?
3. What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.
UPDATE: In a related question here: How long will a temporary MEMORY table persist if I don't drop it (MySQL) I posted a set of updates to the stored proceedure used above which is still recursive, but provides the list of ID's back as a variable, and in a format compatible with FIND_IN_SET() giving me EXACTLY what I needed.
