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 = ($_TD->{new}{user_id});
while ( @ids ) {
my $id = shift @ids;
my $sth = spi_query("SELECT user_id FROM users WHERE parent_id=$id");
while ( my $row = spi_fetchrow($sth) ) {
push @ids, $row->{user_id};
}
my $parent = $id;
my $level = 1;
spi_exec_query("DELETE FROM parents WHERE user_id=$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 = spi_exec_query("SELECT parent_id FROM users WHERE
user_id=$parent");
$parent = $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 = parents.user_id)
WHERE parents.parent_id = 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 = parents.user_id)
WHERE parents.parent_id = ? AND users.user_id = ?;
$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 => "user_id" });
Parents.pm:
__PACKAGE__->belongs_to("user_parent","Users", { user_id => "parent_id" });
Then in Catalyst:
if ( $c->model->search(
{ 'users.user_id' => $target_user_id,
'parents.parent_id' => $c->user->user_id },
{ join => "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 <[EMAIL PROTECTED]>:
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->user )
{
# 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: [email protected]
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
Dev site: http://dev.catalyst.perl.org/
--
Gabriel Vieira
_______________________________________________
List: [email protected]
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
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
_______________________________________________
List: [email protected]
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
Dev site: http://dev.catalyst.perl.org/
