Re^6: many to many with Class::DBI

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

Comment on Re^6: many to many with Class::DBI Select or Download Code

Replies are listed 'Best First'.
Re^*: many to many with Class::DBI by Tomte (Priest) on Feb 12, 2004 at 17:16 UTC
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]

Replies are listed 'Best First'.

Re^*: many to many with Class::DBI
by Tomte (Priest) on Feb 12, 2004 at 17:16 UTC

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]

All

Primary

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]