Grouper Users - Open Discussion List

[Loris Bennett <>]

Hi,

For an org structure with a an automatically generated group ('%:auto')
at each node in the tree we use grouperloader to create roll-up groups
of all the auto groups one level below using this slightly modified
suggestion Chris made:

        select
        gs.name || ':all' as group_name,
        ggv.group_id as subject_id,
        ggv.name as sub_group_name
        from 
        grouper_stems gs,
        grouper_groups_v ggv
        where 
        gs.name like 'sources:employees:'
        and 
        ggv.name similar to gs.name || ':[^:]*:auto'
        and 
        exists (select 1 from grouper_stems gs2 where gs2.name like
        gs.name|| ':%')

(actually this turns out to be a bit slow when there are a lot of
groups, so we in fact now use a different query which makes use of the
fact that grouper_stems contains information about the parent stem of
each entry).

I now have a similar, but slightly different problem. In another tree I
have the students sorted according to the degree they are studying. That
is I have groups like:

        physics:physics:bsc:2008:major:auto

that is :

        department, course name , degree type, version of course,
        major/minor

This tree contains auto-groups only in leaf nodes. I want to create
roll-up groups which contain all the "auto" groups below them in the
hierarchy.

I can solve this using an approach like the one above. I.e. I could select
for each stem all the auto groups for which the stem is a substring of
the group name. However, this is going to run into similar performance
issues to those of the statement above.   

Can anyone think of a better approach?

As a workaround I could generate the "all" groups when I create the stem
hierarchy. Then I could just select the "all" groups of the child nodes
as members of the "all" group in the current node. This would be
analogous to the first scenario I described.

Cheers

Loris

-- 
Dr. Loris Bennett
Computer Centre
Freie Universität Berlin
Berlin, Germany