mysql - SQL Recursion -

i have next tables. groups table contains hierarchically ordered groups , group_member stores groups user belongs to.

groups --------- id   parent_id name  group_member --------- id group_id user_id  id  parent_id  name --------------------------- 1   null       cerebra 2   1          cats  3   2          cats 2.0  4   1          cerepedia  5   4          cerepedia 2.0 6   1          cms   id group_id user_id --------------------------- 1  1        3 2  1        4 3  1        5 4  2        7 5  2        6 6  4        6 7  5        12 8  4        9 9  1        10 

i want retrieve visible groups given user. groups user belongs , children of these groups. example, above data:

user  visible_groups 9     4, 5  3     1,2,4,5,6 12    5  

i getting these values using recursion , several database queries. know if possible single sql query improve app performance. using mysql.

two things come mind:

1 - can repeatedly outer-join table recursively walk tree, in:

select *   my_groups mg1  ,my_groups mg2  ,my_groups mg3  ,my_groups mg4  ,my_groups mg5  ,my_group_members mgm mg1.parent_id = mg2.uniqid (+)   , mg1.uniqid = mgm.group_id (+)   , mg2.parent_id = mg3.uniqid (+)   , mg3.parent_id = mg4.uniqid (+)   , mg4.parent_id = mg5.uniqid (+)   , mgm.user_id = 9 

that's gonna give results this:

uniqid parent_id name      uniqid_1 parent_id_1 name_1 uniqid_2 parent_id_2 name_2  uniqid_3 parent_id_3 name_3 uniqid_4 parent_id_4 name_4 uniqid_5 group_id user_id 4      2         cerepedia 2        1           cats   1        null        cerebra null     null        null   null      null       null   8        4        9 

the limit here must add new join each "level" want walk tree. if tree has less than, say, 20 levels, away creating view showed 20 levels every user.

2 - other approach know of create recursive database function, , call code. you'll still have lookup overhead way (i.e., # of queries still equal # of levels walking on tree), overall should faster since it's taking place within database.

i'm not sure mysql, in oracle, such function similar 1 (you'll have change table , field names; i'm copying did in past):

create or replace function gouplevel(wo_id integer, uplevel integer) return integer begin   declare     iresult integer;     iparent integer; begin   if uplevel <= 0     iresult := wo_id;   else     select parent_id     iparent     wotree     id = wo_id;         iresult := gouplevel(iparent,uplevel-1);  --recursive   end;   return iresult;   exception when no_data_found     return null;   end; end gouplevel; /