Re: Performing a join...?

[prev] [thread] [next] [Date index for 2004/12/16]

jay@xxxxxxx.xxx writes:
 > My first post - how exciting!?
 > 
 > I've recently begun using Class::DBI to handle a database containing a table of modules, a table of users and a table of module_permissions:
 > 
 > mysql> desc module;
 > +-------------+--------------+------+-----+---------+-------+
 > | Field       | Type         | Null | Key | Default | Extra |
 > +-------------+--------------+------+-----+---------+-------+
 > | module_id   | varchar(32)  |      | PRI |         |       |
 > | class       | varchar(16)  | YES  |     | NULL    |       |
 > | country     | char(3)      | YES  |     | NULL    |       |
 > | module      | varchar(32)  | YES  |     | NULL    |       |
 > | paf         | varchar(255) | YES  |     | NULL    |       |
 > | description | text         | YES  |     | NULL    |       |
 > +-------------+--------------+------+-----+---------+-------+
 > 6 rows in set (0.01 sec)
 > 
 > mysql> desc user;
 > +-----------------+--------------+------+-----+---------+-------+
 > | Field           | Type         | Null | Key | Default | Extra |
 > +-----------------+--------------+------+-----+---------+-------+
 > | user_id         | varchar(32)  |      | PRI |         |       |
 > | password        | varchar(32)  | YES  |     | NULL    |       |
 > | company         | varchar(50)  | YES  |     | NULL    |       |
 > | contact_name    | varchar(50)  | YES  |     | NULL    |       |
 > | contact_tel     | varchar(20)  | YES  |     | NULL    |       |
 > | contact_email   | varchar(50)  | YES  |     | NULL    |       |
 > | ip              | varchar(255) | YES  |     | NULL    |       |
 > | default_country | char(3)      | YES  |     | NULL    |       |
 > | created_date    | datetime     | YES  |     | NULL    |       |
 > | clear_password  | varchar(20)  | YES  |     | NULL    |       |
 > +-----------------+--------------+------+-----+---------+-------+
 > 10 rows in set (0.00 sec)
 > 
 > mysql> desc module_permission;

I would name this table "user_module".

 > +-----------+-------------+------+-----+---------+-------+
 > | Field     | Type        | Null | Key | Default | Extra |
 > +-----------+-------------+------+-----+---------+-------+
 > | module_id | varchar(32) |      | PRI |         |       |
 > | user_id   | varchar(32) |      | PRI |         |       |
 > +-----------+-------------+------+-----+---------+-------+
 > 2 rows in set (0.00 sec)
 > 
 > What I want to do is perform a query that will (for instance) return the 'module_id' for all modules with country='uk' and paf='/store/data' that are available to a user with a user_id='jay'.

CDBI can help you to model the many-to-many relationship between users
and modules:

UserModule->has_a(user_id => 'User');
UserModule->has_a(module_id => 'Module);
User->has_many(modules => [ 'UserModule' => 'module_id' ]);
Module->has_many(users => [ 'UserModule' => 'user_id' ]);

Now you can write things like:

my $user = ...
foreach my $module ($user->modules) {
  print $module->description;
  ...
}

my $module = ...
foreach my $user ($module->users) {
  print $user->company;
  ...
}

$user->add_to_modules({
  module_id => module->id,
});

That last bit will give a user a new module.

In order to involve the country and paf attributes, you'll either have
to write something similar to your find_modules() (though perhaps
simplified by the methods that CDBI set up for you), or -- more
efficiently -- drop down to the SQL level with CDBI's set_sql() and
run the join that way.

I can't see a clever way to dynamically include module attributes
using set_sql, but perhaps someone else can suggest that.

Kings

 > Is there a simple way to do this? And if not am I right in thinking that the best way is to do something like:
 > 
 > package ModulePermission;
 > 
 > use base Functions::DBI;
 > 
 > __PACKAGE__->table("module_permission");
 > __PACKAGE__->columns(All => qw/ module_id user_id / );
 > __PACKAGE__->has_a(module_id => 'Functions::Module');
 > __PACKAGE__->has_a(user_id => 'Functions::User');
 > 
 > sub find_modules
 > { my ($self, $user_id, $mod_args) = @_;
 >   my @modules;
 >   foreach my $mp ($self->search(user_id => $user_id))
 >   { my $found = 1;
 >     while (my ($key, $val) = each %$mod_args)
 >     { $found &= eval { $mp->module_id->$key eq $val };
 >     }
 >     push @modules, $mp if $found;
 >   }
 >   return @modules;
 > }
 > 
 > Cheers
 > Jay