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

Hello fellow monks,
hope this is the right place to post this...

I recently found out about Class::DBI and I'm loving it.

While attempting to represent a Many-To-Many relationship (for my shopping cart) I ran into a problem:
my table has 2 fields (user_id and item_id), I can add a relationship by using find_or_create({user_id => 1, item_id => 2}) however if I try to delete a relationship I run into lots of trouble.

Since user_id is taken as the "Primary" key if I Cart->delete(user_id => 1, item_id => 2) all relationships containing user_id = 1 are deleted (I want only the one with item_id = 2 to go away). I thought of creating a junk field in the DB and labeling it as the primary key but that is a waste of a field that will never actually be used. I also tried using Class::DBI::Join but it is broken and not maintained.

Please suggest a module or method to get around this, thanks in advance!!

Replies are listed 'Best First'.
Re: Many-to-many with Class::DBI
by jgallagher (Pilgrim) on May 12, 2003 at 01:05 UTC
    The "feature" you're talking about is called cascading delete, and according to this message, the author doesn't see any reason to turn off cascading delete. As far as other modules, I think Class::DBI is the easiest to use, but there is a list and comparison of others at http://poop.sourceforge.net/.
[reply]
Re: Many-to-many with Class::DBI
by edoc (Chaplain) on May 12, 2003 at 00:59 UTC

    I would have to suggest adding a proper primary key (auto increment field or similar). The primary key has to identify a single record in the table doesn't it? So your primary key in this case would need to consist of both the user_id and item_id fields.

    Not sure what you're building the cart for, but I discovered early on that you'll also then come to grief if you had say different sizes, colours, etc for an item which might mean that a customer will want to have 2 of the same item in their cart (different colours or sizes).

    The idea of a "junk" field is actually a good one, and I wouldn't call it junk. In the future you will probably find it very handy to have an id that simply identifies the record and doesn't really relate to the data in the record. I've learnt this way too late, and am slowly converting lots of tables to include this.

    The other field I'd recommend is a timestamp that is updated whenever the record is added/changed so that it's easy to tell how long it's been since it's been accessed.

    cheers,

    J

[reply]
Re: Many-to-many with Class::DBI
by Ovid (Cardinal) on May 12, 2003 at 03:03 UTC

    He Who Must Not Be Named wrote I also tried using Class::DBI::Join but it is broken and not maintained.

    How is it broken? Can you show us a small test case that demonstrates your problem? I have no doubt that it might be broken, but if it does what you want with the exception of a bug fix, then that might be the way to go. The Class::DBI mailing list is very active and perhaps someone there can help you.

    Cheers,
    Ovid

    New address of my CGI Course.
    Silence is Evil (feel free to copy and distribute widely - note copyright text)

[reply]

      From the Class::DBI mailing list..

      "Michael G Schwern wrote:
      >> Can anyone confirm that Class::DBI::Join is working OK with cdbi v0.92?

      I doubt it does. I does some less-than-savory jiggery pokery to work.

      Anyhow, Class::DBI::Join is largely moot now that there's Class::DBI->has_many.
      I may pull it from CPAN as I don't intend on maintaining it."

[reply]
Re: Many-to-many with Class::DBI
by jeffa (Bishop) on Aug 10, 2003 at 21:40 UTC
    user: id name
    item: id name
    cart: user item
     Tables user and item both have a primary key named id (i recommend you make this an auto incremented unsigned integer). The two fields in the cart table have their _id extensions removed because Class::DBI prefers them that way. They are still foreign keys associated with their respective tables' ids. With all of that out of the way, here is the (untested) code: 
    package CART::user;
use base qw(CART::DBI);
__PACKAGE__->table('user');
__PACKAGE__->columns(All => qw(id name));
__PACKAGE__->has_many(items => [qw(CART::item item)]);

package CART::item;
use base qw(CART::DBI);
__PACKAGE__->table('item');
__PACKAGE__->columns(All => qw(id name));
__PACKAGE__->has_many(users => [qw(DVD::user user)]);

package CART::cart;
use base qw(CART::DBI);
__PACKAGE__->table('cart');
__PACKAGE__->columns(Primary => qw/user item/);
__PACKAGE__->has_a(user => 'DVD::user');
__PACKAGE__->has_a(item => 'DVD::item');

package main;

for my $user (CART::user->retrieve_all) {
   my $items = $user->items;
   print $user->name;
   print " has no items\n" and next unless $items;
   print "has these items:\n";
   print $_->name, "\n" for @$items;
}

    [download]
    Better late than never. ;)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]

Back to Seekers of Perl Wisdom