[Catalyst] Users hierarchy

[Jonathan Hall]

I make no claim at being an expert, but here's what we do... If there's =

a better way, I'm all ears!

We use two tables.  A Users table, and a Parents table that acts as a =

map between users and all of their possible parents.

<PostgreSQL>
CREATE TABLE users (
       user_id      SERIAL PRIMARY KEY,
       parent_id   INT REFERENCES users(user_id)
       ...
);

CREATE TABLE parents (
       user_id      INT REFERENCES users(user_id) ON DELETE CASCADE,
       parent_id  INT REFERNECES users(user_id) ON DELETE CASCADE,
       level          INT,
       PRIMARY KEY (user_id,parent_id)
);
</PostgreSQL>

Somewhere you need logic that populates the "parents" table any time a =

user is added/deleted, or re-parented (if supported by your =

application).  The "parents" table should have one entry for every =

parent/child relationship in existence, including each users's =

relationship with themself.  Yes, this means lots of duplicate data.

Suppose your users tree contains 5 users:

User1
 +-- User2
 +-- User3
    + User4
    + User5

Your "users" table will look like this:

 user_id | parent_id
---------+-----------
       1 |
       2 |         1
       3 |         1
       4 |         3
       5 |         3

And your "parents" table should look like this:

 user_id | parent_id | level
---------+-----------+-------
       1 |         1 |     0
       2 |         1 |     1
       2 |         2 |     0
       3 |         1 |     1
       3 |         3 |     0
       4 |         1 |     2
       4 |         3 |     1
       4 |         4 |     0
       5 |         1 |     2
       5 |         3 |     1
       5 |         5 |     0


The "level" column represents how far away you are from the "top" level =

user.  If your needs are simple enough, this column may not be strictly =

necessary.  If your needs are complex enough, you may also need an =

opposite level column--a "from_top" and "from_bottom" may be more =

appropriate.

We keep this table up-to-date with a plperl stored procedure:

<PostgreSQL>
CREATE FUNCTION update_parents_table() RETURNS TRIGGER AS $$
my @ids =3D ($_TD->{new}{user_id});
while ( @ids ) {
  my $id =3D shift @ids;
  my $sth =3D spi_query("SELECT user_id FROM users WHERE parent_id=3D$id");
  while ( my $row =3D spi_fetchrow($sth) ) {
    push @ids, $row->{user_id};
  }
  my $parent =3D $id;
  my $level =3D 1;
  spi_exec_query("DELETE FROM parents WHERE user_id=3D$id") if =

$_TD->{event} eq 'UPDATE';
  spi_exec_query("INSERT INTO parents (user_id,parent_id,level) VALUES =

($id,$id,0)");
  while ( $parent ) {
    my $rv =3D spi_exec_query("SELECT parent_id FROM users WHERE =

user_id=3D$parent");
    $parent =3D $rv->{rows}[0]->{parent};
    spi_exec_query("INSERT INTO parents (user_id,parent_id,level) VALUES =

($id,$parent,$level)");
    $level++;
  }
}
return;
$$ LANGUAGE plperl;

CREATE TRIGGER update_parents_table
    AFTER INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE PROCEDURE update_uid_cache();
</PostgreSQL>

The same thing should be easily accomplished in any other stored =

procedure language, or even in DBIC or your application.

Then, to do the query (the part you're actually trying to do!) it's just =

a matter of a join.  To select all of the users that are at user 3 or =

lower in the tree (this will include user 3, 4, and 5 from my example):

SELECT * FROM users JOIN parents ON (users.user_id =3D parents.user_id) =

WHERE parents.parent_id =3D 3;
 user_id | parent_id | user_id | parent_id | level
---------+-----------+---------+-----------+-------
       4 |         3 |       4 |         3 |     1
       5 |         3 |       5 |         3 |     1
       3 |         1 |       3 |         3 |     0


Or for your requirement... ( if ( user_id is =

child,grandchild,gradgrandchild.... of logged in $c->user ) );

SELECT 1 FROM users JOIN parents ON (users.user_id =3D parents.user_id) =

WHERE parents.parent_id =3D ? AND users.user_id =3D ?;
$c->user->user_id, $target->user_id.

In DBIC (Someone can probably think of better relationship names):

Users.pm:
__PACKAGE__->has_many("parents","Parents", { parent_id =3D> "user_id" });

Parents.pm:
__PACKAGE__->belongs_to("user_parent","Users", { user_id =3D> "parent_id" }=
);


Then in Catalyst:

if ( $c->model->search(
    {  'users.user_id' =3D> $target_user_id,
        'parents.parent_id' =3D> $c->user->user_id },
    { join =3D> "parents" })->first ) {
    # Has permission
} else {
    # Does not have permission
}

That's it... in a nutshell.  Perhaps a bit daunting if it's your first =

go at something like this, but once it makes sense in your head, it =

should be pretty straight-forward to implement and use.

--
Jonathan


Gabriel Vieira wrote:
> I usually use a subid to identify child's parent as you do. So I run
> bottom to top to get to the major user. With this I can see if the
> some ID have access to other.
>
> This may be slow. Let's see what gurus have to say. :)
>
> 2008/6/2 jakac <jakac at iprom.si>:
>   =

>> Hello!
>>
>> I need a help on building a model for user that has a permission to edit
>> other users that were created by this user or any of his children,
>> grandchildren etc.
>> (difficult sentence, I know)
>>
>> To make this a little more understandable here's my database table:
>>
>> - user_id
>> - username
>> - password
>> - various other data such as fname, lname, address etc.
>> - parent_id
>>
>> Column "parent_id" has a value of "user_id" that created one user. There=
 is
>> also
>> a "superadministrator" with parent_id '0' that can edit everybody.
>>
>> Now I would like to build a model that I can use in my controllers like:
>>
>> if ( user_id is child,grandchild,gradgrandchild.... of logged in $c->use=
r )
>> {
>>     # has permisson to edit
>> } else {
>>     # doesn't have a permission to edit
>> }
>>
>> Example:
>> - superadmin
>> |__- foo
>> |__- bar
>> |____- john
>> |______- doe
>>
>> In this example:
>> - superadmin can edit anybody,
>> - bar can edit john & doe
>> - john can edit only doe
>> - foo can't edit anybody since he has no children
>> And there can be unlimited levels of users...
>>
>> There is no problem with permission to edit first child since I can just
>> compare
>> logged in user's ID with edited user's parent_id but when edited user is
>> grandchild,
>> grandgrandchild, (grand * n) child of $c->user then I need some kind of
>> model
>> to return true/false value.
>>
>> I've never done that and I am also new to Catalyst so any help would be
>> appreciated.
>> Thank you!
>>
>>
>> JakaC.
>> _______________________________________________
>> List: Catalyst at lists.scsys.co.uk
>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>> Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.=
uk/
>> Dev site: http://dev.catalyst.perl.org/
>>
>>
>>     =

>
>
>
> --
> Gabriel Vieira
>
> _______________________________________________
> List: Catalyst at lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.u=
k/
> Dev site: http://dev.catalyst.perl.org/
>   =


-- =

Inbound and outbound email scanned for spam and viruses by the

DoubleCheck Email Manager: http://www.doublecheckemail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20080602/2d421=
e90/attachment.htm