Performing a join...?

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

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;
+-----------+-------------+------+-----+---------+-------+
| 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'.

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