Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Unlimited groups/categories

by Anonymous Monk
on Jun 01, 2008 at 01:16 UTC ( #689481=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

How would you go about creating a database that has endless groups unlimited levels deep? Ie: Your business has 100 teams and some teams have subteams.. and those subteams can have unlimited sub teams as well.

I have it set up where my database has a subcat field for the parent group in which it exists. I have that part all taken care of.<p. The question then is, when I print out all groups, how do I keep all the groups and subgroups together? For example

id gname subgroup 1 red team 0 2 orange team 0 3 black team 0 4 girl team 2 5 guy team 2 6 heavy weights 5
and have the result be
red team orange team > girl team > guy team >> heavyweights black team
The trick is, I have to be able to advance-sort these teams by many different fields dynamically (id asc, name desc, size desc...) and I'm completely lost on how to go about this.

This is MySQL if anyone needs to know what database I'm trying to use.

Replies are listed 'Best First'.
Re: Unlimited groups/categories
by friedo (Prior) on Jun 01, 2008 at 01:48 UTC

    For trivial tree-processing problems, a recursive solution is generally easiest. This might not be the best way if your tree is truly gigantic, but it should work in most cases. This is untested but should get you on the right track:

    my $dbh = ... # set up database sub display_groups { my ( $parent, $level ) = @_; # get all children of this parent my $sth = $dbh->prepare("SELECT id, gname FROM groups WHERE subgroup=? ORDER BY gname"); $sth->execute( $parent ); # display each child and its descendants while( my $row = $sth->fetchrow_hashref ) { print " " x $level; # indent print $row->{gname}, "\n"; display_groups( $row->{id}, $level + 1 ); } } # start with children of group 0 display_groups( 0, 0 );

    Update: Added ORDER BY clause.

Re: Unlimited groups/categories
by BrowserUk (Patriarch) on Jun 01, 2008 at 02:16 UTC

    Hierarchal data, and relational DBs do not mix well. There are hackish solutions involving adjacency lists and several variations, but they are all inefficient and clumsy at best, and downright friggin' awkward at worst.

    See MySQL:Managing hierachal data for the skinny.

    It gets easier with Stored Procedures, but it can still get slow and messy. Sometimes, it is worth at least asking the question: is there a better way to persist my data than a RDBMS, and hierarchal data is one such time.

    The primary questions are:

    • Do you need concurrancy?
    • Do you need performance?
    • What volumes are you dealing with?

    A realistic take on the answers to those questions can go a long way to suggesting other possible alternatives.

    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
Re: Unlimited groups/categories
by psini (Deacon) on Jun 01, 2008 at 01:50 UTC

    I'm not sure I've understand your problem: it seems you want an algorithm for printing your groups tree.

    The most obvious way to do so is to write a recursive sub which print all the groups with a given value of subgroups:

    You define a sub, let's call it "scan" which takes two arguments "$subGroup" and "level". The sub does a SQL select of all groups with subgroup=$subgroup ordered as you like; for each row of the dataset, it prints gname preceeded by $level ">" and then calls itself passing as arguments the value of id for the row and $level+1.

    Careful with that hash Eugene.

Re: Unlimited groups/categories
by Anonymous Monk on Jun 01, 2008 at 01:30 UTC

    There is a field called disporder so the main teams (subgroup 0) will always print in the way I want them.

    What I need is a way to print all subteams of subteams.. whatever exists for each team. And all the subteams can be a-z order.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://689481]
Approved by friedo
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2022-12-10 09:35 GMT
Find Nodes?
    Voting Booth?

    No recent polls found