Josh Josh - 4 months ago 22
MySQL Question

mysql recursive self join intersecting columns

Say I have a starting table (derived) that is like this...

-------------------------------------------------------
| UsergroupID | ParentID | PermissionIDs |
-------------------------------------------------------
| 1 | 0 | 1 |
| 1 | 0 | 2 |
| 1 | 0 | 3 |
| 1 | 0 | 4 |
| 1 | 0 | 5 |
| 1 | 0 | 6 |
| 2 | 1 | 1 |
| 2 | 1 | 8 |
| 2 | 1 | 9 |
| 3 | 1 | 3 |
| 3 | 1 | 8 |
| 3 | 1 | 2 |
-------------------------------------------------------


and I'm looking to get an ending result set that looks like this

-------------------------------------------------------
| UsergroupID | ParentID | PermissionID |
-------------------------------------------------------
| 1 | 0 | 1 |
| 1 | 0 | 2 |
| 1 | 0 | 3 |
| 1 | 0 | 4 |
| 1 | 0 | 5 |
| 1 | 0 | 6 |
| 2 | 1 | 1 |
| 3 | 1 | 3 |
| 3 | 1 | 2 |
-------------------------------------------------------


which is basically doing a recursive lookup on the parent id and then intersecting (inner joining) the values in the PermissionID column . So a child can never have more permissions then a parent.

I've looked up stuff on user defined functions (thinking I could wrap a udf around a column and have it intersect recursively based on a parent id) but that didn't get me very far. The only thing I can really think of is not doing it db side but doing it with server side code.

Solarflare -- Here is what I just tried using your script... this worked!

delimiter $$
CREATE PROCEDURE prcPermCleanup5()
BEGIN
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (usergroupID INT, parentID INT, StoreID INT) ENGINE=MEMORY;
INSERT INTO table1 VALUES
(1,0,1),
(1,0,2),
(1,0,3),
(1,0,4),
(2,1,1),
(2,1,2),
(2,0,5),
(3,2,2),
(3,2,7),
(4,1,1),
(4,1,2),
(5,4,1),
(5,4,8),
(6,2,1),
(6,2,6);
REPEAT
DELETE entry.*
FROM table1 entry
LEFT JOIN table1 parent
ON entry.parentID = parent.usergroupID
AND entry.`StoreID` = parent.StoreID
WHERE parent.usergroupID IS NULL
AND NOT entry.parentID = 0;
UNTIL row_count() = 0 END REPEAT;
SELECT * FROM table1;
END $$
delimiter ;

Answer

It's not possible to do that in one single query (unless maybe under some special conditions), but you can just repeat a clean-up query to do a recursion that way.

If your cleanup is a one-time thing, you can just run the following query several times until nothing changes anymore (you need at most depth of tree - 1 runs):

delete entry.*
from table1 entry
left join table1 parent
on entry.parentID = parent.usergroupID
and entry.permissionIDs = parent.permissionIDs
where parent.usergroupID is null
and not entry.parentID = 0;

You can automate that repetition in a procedure, e.g.

delimiter $$
create procedure prcPermCleanup()
begin
  repeat
    delete entry.*
    from table1 entry
    left join table1 parent
    on entry.parentID = parent.usergroupID
    and entry.permissionIDs = parent.permissionIDs
    where parent.usergroupID is null
    and not entry.parentID = 0;
  until row_count() = 0 end repeat;  
end $$
delimiter ;

call prcPermCleanup;

As a side-note:

You might want to normalize your data, e.g. have a seperate table for your permissions:

table permissions: usergroupID | permissionID

table tree: usergroupID | parentID 

In your current table, you have the same information (the information, which parentID is the parent of a usergroupID) several times in your table, aka denormalized. A practical consequence of that would be that you could have two different parentID for the same usergroupID, which would usually be undefined in a tree.