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

[jakac]

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 =3D> "integer", default_value =3D> =

"nextval('public.user_user_id_seq'::text)",  is_nullable =3D> 0, size =3D> =

4,  },

  "username",
  {  data_type =3D> "character varying",    default_value =3D> undef,    =

is_nullable =3D> 0,    size =3D> 32,  },

  "password",
{  data_type =3D> "character varying", default_value =3D> undef, is_nullabl=
e =

=3D> 0, size =3D> 128 },

# ........ other user data ......

  "parent_id",
  { data_type =3D> "integer", default_value =3D> undef, is_nullable =3D> 1, =

size =3D> 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!

JC.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080604/07b=
1e471/attachment.htm