jdtoronto has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed monks,

I cannot understand the 'mapping' method described in the documentation. Here is what I have - starting with three tables: 

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL default '',
  `lastname` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) TYPE=MyISAM;
insert  into `user` values 
(1, 'John', 'Day'), 
(2, 'Jacqui', 'Burke'), 
(3, 'Brenda', 'Day'), 
(4, 'Cassie', 'Cat'), 
(5, 'Steven', 'Burke'), 
(6, '', '');

CREATE TABLE `role` (
  `role_id` int(11) NOT NULL auto_increment,
  `rolename` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`role_id`)
) TYPE=MyISAM;
insert  into role values 
(1, 'Typist'), 
(2, 'Programmer'), 
(3, 'Cleaner');

CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL default '0',
  `role_id` int(11) NOT NULL default '0'
) TYPE=MyISAM;
insert  into user_role values 
(1, 1), 
(1, 2), 
(1, 3), 
(2, 2), 
(3, 1), 
(3, 2), 
(0, 0);

[download]
My Class::DBI subclass: 
package Test::DBI;
use base 'Class::DBI';

Test::DBI->set_db('Main', 'DBI:mysql:appsys2', '****', '****');

package TestDBI::userrole;
use base 'Test::DBI';
__PACKAGE__->table('user_role');
__PACKAGE__->columns(All => qw(user_id role_id));
__PACKAGE__->has_a( role_id => TestDBI::role );
__PACKAGE__->has_a( user_id => TestDBI::user );

package TestDBI::user;
use base 'Test::DBI';
__PACKAGE__->table('user');
__PACKAGE__->columns(All => qw(user_id firstname lastname));

__PACKAGE__->has_many(haveroles => ['TestDBI::userrole' => 'role_id'] 
+);

package TestDBI::role;
use base 'Test::DBI';
__PACKAGE__->table('role');
__PACKAGE__->columns(All => qw(role_id rolename));

1;

[download]
and my test code: 
#!/usr/local/bin/perl;

use warnings;
use strict;

use Data::Dumper;
use Test::DBI;

my $testuser = TestDBI::user->retrieve( 1 );
print "user with id 1 is: ". $testuser->firstname ."\n";
print "and has roles:\n";
my @roles = $testuser->haveroles;

print Dumper( \@roles );
#foreach (@roles) {
#    print "role: " . $_->id . " and " . $_->rolename . "\n";
#}
exit;

[download]
I want to see the list of roles for the user, but, all I get is this: 
user with id 1 is: John
and has roles:
 is not a column of TestDBI::userrole at test.pl line 12
[root@posiedon3 www]#

[download]
I am sure I have done something stupid here. But the problem is that I just cannot figure out what the 50 words in the POD is actually saying! Maybe someone can point me to some more documentation.

jdtoronto

Replies are listed 'Best First'.
Re: many to many with Class::DBI
by Tomte (Priest) on Feb 12, 2004 at 13:04 UTC

    replace 

    __PACKAGE__->columns(All => qw(user_id role_id));

    [download]
    with 
    __PACKAGE__->columns(Primary => qw(user_id role_id));

    [download]
    and your code should work (it does here ;-).

    an error likely to be overlooked! I spotted it reading the Class::DBI documentation; I don't know what the difference in the generated code is, and frankly: I don't care ;-) It's a cool module!

    Edit:tested with perl 5.8.3 and Class::DBI 0.95. without the mentioned change I didn't get your error but wrong results (all three roles as 'typist') instead.

    regards,
    tomte

    Hlade's Law:

    If you have a difficult task, give it to a lazy person --
    they will find an easier way to do it.

[reply]
[d/l]
[select]

      I don't know what the difference in the generated code is, and frankly: I don't care ;-)

      You should care. That code makes both user_id and role_id a primary key, which means you now have to specify both when you make a call to retrieve. Further, primary keys have a specific definition under relational database theory, and you can't go making just anything a primary key or Bad Things will happen.

      ----
      : () { :|:& };:

      Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
        You should care....

        No, I shouldn't :-), I could!
        because, at least in the code I tested, I created the database with SQL myself, knowing about primaray keys, foreign keys etc.

        and after that I used a Perl-Module, namely Class::DBI, to access this DB, according to it's documentation; the documentation stated clearly that many-to-many relations aren't supported directly, but how to circumvent this fact using the mappings available via Class::DBI

        So I should care about my data-definition, you are right with that, but shouldn't care about the internals of a module I use, if this works as stated in the documentation, and nothing else was my intention to express in my first post.

        regards,
        tomte

        Hlade's Law:

        If you have a difficult task, give it to a lazy person --
        they will find an easier way to do it.

[reply]
        So how fix without Primar?
[reply]
      I updated to 0.95 this morning and the error went away. Your suggested change got me the data I wanted, thank you. Now I can go back and fix my reall application and make some notes in my files about CDBI.

      ...john

[reply]
Re: many to many with Class::DBI
by Anonymous Monk on Feb 12, 2004 at 13:01 UTC
    What version do you have? You shouldn't be getting that error with the code you have.
[reply]
      I was using 0.94, which produced the error. I upgraded to 0.95 this morning and the error went away, but results were still incorrect.

      jdtoronto

[reply]

Back to Seekers of Perl Wisdom