[Dbix-class] DBIx::Class and hierarchy (trees)

[John Napiorkowski]

--- On Wed, 6/4/08, jakac <jakac at iprom.si> wrote:

> From: jakac <jakac at iprom.si>
> Subject: [Dbix-class] DBIx::Class and hierarchy (trees)
> To: dbix-class at lists.scsys.co.uk
> 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/dbix-class@lists.rawmode.org