in reply to Re: Re: Re: Re: many to many with Class::DBI
in thread many to many with Class::DBI

No, the code worked because the code (as well as the exmple data) is more restrictive than the data-definition. You can define the n-m table as 

mysql> create table user_role (
    -> user_id int(11) not null default '0',
    -> role_id int(11) not null default '0',
    -> primary key (user_id, role_id),
    -> foreign key user_fk (user_id) references user (id),
    -> foreign key role_fk (role_id) references role (id) 
    -> );

[download]
the data fits, the program works, and the Class::DBI definition actualy meets the data-definition as close as possible (AFAI understand it at a quick glance), but given the example-data, nothing really changed. If the data-definition allows for more than the program actualy does, thats undesirable and bad style and a big problem in the future, but not a problem now.

may well be that I don't understand you at all, I'm really tired and the finer points of english conversation escape me all the time....

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.

Replies are listed 'Best First'.
Re^6: many to many with Class::DBI
by hardburn (Abbot) on Feb 12, 2004 at 16:57 UTC

    I see it now. The orginal data defition doesn't use any primary keys on the join table at all. But I don't think making both fields primary keys is the right way to go. Instead, I usually create a third field that is a primary key all on its own:

    mysql> create table user_role (
    -> id      int(11) not null primary key auto_increment, 
    -> user_id int(11) not null default '0',
    -> role_id int(11) not null default '0',
    -> foreign key user_fk (user_id) references user (id),
    -> foreign key role_fk (role_id) references role (id) 
    -> );

    [download]

    The OP's Class::DBI code was making user_id the primary key, which didn't match up the data definition either (All makes the first column specified the primary key). The new code should be:

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

    [download]

    With the has_a relationships that are noted in the orginal.

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

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]

      Class::DBI's Documentation:

      Consider the case of Films and Actors, with a linking Role table. First of all we'll set up our Role class: 
              Role->table('role');
        Role->columns(Primary => qw/film actor/);
        Role->has_a(film => 'Film');
        Role->has_a(actor => 'Actor');

      [download]
      I merely used the documentation, and it worked; but you're right with the id as new primary key, and Class::DBI workes as it should with this data-definition and All instead of Primary. (Tested using innodb, foreign keys were in effect :-)

      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]

In Section Seekers of Perl Wisdom