Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
MySQL does not support "table functions" - functions or procedures that can be inside a select statement. I don't think what you want to do is simply not possible in MySQL –  a_horse_with_no_name Jan 31 at 12:53
    
@a_horse_with_no_name I mentioned that in my third question, you just confirmed it, however, I'm hoping there's a combination of stored function + stored proceedure, or some series of requests in order that will produce the output I'm looking for. –  oucil Jan 31 at 13:06
    
To my knowledge this is not possible. MySQL is just too limited for that. –  a_horse_with_no_name Jan 31 at 13:07

1 Answer 1

up vote 1 down vote accepted

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?

No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.

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.

I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.

The algorithm goes like this

  • STEP 01 : Start with an empty queue
  • STEP 02 : Dequeue Node From the Front of the Queue
  • STEP 03 : Enqueue All Children of the Latest Node
  • STEP 04 : Process Info From the Latest Node
  • STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
  • STEP 06 : All Done

This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?

I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:

  • GetParentIDByID
  • GetAncestry
  • GetFamilyTree

The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
| notes      | text         | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

Please read the code carefully and apply the principles.

Give it a Try !!!

share|improve this answer
    
FIRST: I'm certainly going to take a closer look at your solution, I've definitely gotten the hint from you and others in my reading that states CURSOR should only be used as a last resort or in very specific circumstances. I have a category system separate from this using a mixed adjacency + lineage column technique which has proven flexible, but could still use some tuning. –  oucil Feb 1 at 13:07
    
SECOND: In this particular case, my usergroup hierarchies will rarely if ever include more than 3 or 4 nodes. Given that my result set would be so small, do you still the 3-function solution is the most performant? I'm going to do some testing to see but I'd be interested to know if you think the benefits of the 3-function system would be realized on a small set like this. –  oucil Feb 1 at 13:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.