I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables
For simplicity sake, lets say users only contains one field
user_id, and groups only contains
relationships uses three fields:
related_group_id to represent both user to group and group to group relationships.
A dynamically populated group hierarchy would be something like:
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 (
A,1 B,1 C,1 D,2 E,2 F,2
The dynamic Group
3 would have two group to group relationship records (
To illustrate my problem better lets add a Dynamic Group
4 and have it include Group
Piecing together what I've learned of MySQL
CURSOR's, I've created the following stored procedure called
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...
...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.