[Dbix-class] DBIx::Class and hierarchy (trees)
John Napiorkowski
jjn1056 at yahoo.com
Wed Jun 4 15:25:30 BST 2008
--- 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
More information about the DBIx-Class
mailing list