--- On Wed, 6/4/08, jakac <[EMAIL PROTECTED]> wrote: > From: jakac <[EMAIL PROTECTED]> > Subject: [Dbix-class] DBIx::Class and hierarchy (trees) > To: [email protected] > Date: Wednesday, June 4, 2008, 7:26 AM > I am coding application using Perl Catalyst framework which > uses > DBIx::Class for database manipulation. I also have a table > of users that > are ordered in some kind of a tree. My Postgres table > schema looks like: > > Columns in table "users": > - user_id (primary key, integer) > - username (varchar) > - password (varchar) > (some other columns with user data) > - parent_id (integer) > > > so my schema looks like: > __PACKAGE__->load_components("Core"); > __PACKAGE__->table("users"); > __PACKAGE__->add_columns( > "user_id", > { data_type => "integer", default_value > => > "nextval('public.user_user_id_seq'::text)", > is_nullable => 0, size => > 4, }, > > "username", > { data_type => "character varying", > default_value => undef, > is_nullable => 0, size => 32, }, > > "password", > { data_type => "character varying", > default_value => undef, is_nullable > => 0, size => 128 }, > > # ........ other user data ...... > > "parent_id", > { data_type => "integer", default_value > => undef, is_nullable => 1, > size => 4 }, > ); > __PACKAGE__->set_primary_key("user_id"); > > > So each element in this table is let's say $user ... So > I am looking for > a solution how to write the following functions: > > 1.) get_all_children - a function that would return a > reference to all > children, grandchildren, grandgrandchildren (unlimited > levels) of a > single $user ... Function should also be able to handle > "order by" (e.g. > "order by username desc"), limit (e.g. > "limit 10") and offset (e.g. > "offset 20") so the returned data would be > suitable to deliver on a webpage > > 2.) is_parent_of - a function that would return true/false > if $user is a > parent,grandparent,grandgrand....(again unlimited levels) > of $someotheruser > > I understand the concept - recursive function calls will be > needed - but > I don't know how to code these functions > so they would be reusable and without any (levels/depth) > limits. Mostly > all data is only 2 - 3 levels deep but > I would like to have functions that would be able to handle > any depth. > > Until now I only used simple select/update/insert calls > with DBIx so > this whole trees concept > is just too much for me... > > Thank you for any solution!
You might want to look at: http://search.cpan.org/dist/DBIx-Class-Tree/ Which is a bit of a different solution than the one you outlined, but I've personally had too much trouble with self-referential tables and prefer this kind of solution. I find it's easier to descend through multiple levels without creating insane joins on joins. I know you are using postgresql, but there's a decent explanation of this method at: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html --John > > JC._______________________________________________ > List: > http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: > http://www.grokbase.com/group/[EMAIL PROTECTED] _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
