[CDBI] delete from lookup table

WulfDirk.Leuschner at sanofi-aventis.com WulfDirk.Leuschner at sanofi-aventis.com
Thu Oct 27 09:02:14 BST 2005

> Peter Speltz [peterspeltz at gmail.com] wrote:
>  What i have seen a lot is  is the use or "delete_all" on the results
>  from a search.
>  $class->search( id => $related_id)->delete_all;

>  and if a "my_method" is a has_many accessor i think you can say;
>  $self->my_method(id => $id)->delete_all;

Unfortunately this does not work - it returns the mapped objects themselves. Thus, using
this code you end up deleting both the entry in your lookup table *and* the the record for
the mapped object.

>  I'm not sure if has_many method will return an iterator in scalar
>  context or not.

In scalar context it is an iterator, in list context a list of the proper objects.

> Bill Moseley [moseley at hank.org] wrote:
>  The use where I needed something like this is when updating, say, a
>  user form where there's collection of checkboxes for "Roles" which are
>  maintained in a many_to_many table.

This is exactly my scenario - the tables (simplified) are:

   table USER with columns 'id', 'name'
   table USER_GROUP_MAP with columns 'user_id', 'group_id'
   table GROUP with columns 'id', 'name'

The method containing the mapping information is called 'mapped_groups'.

The code snippet Bill provided (see below) does the right thing - and I did it more or less in the same but a non-generic way by providing an extra sub in my USER class. The thing I do not really like about Bill's code is the need to dig into the object's hash structure - because there is no method available to retrieve the necessary information. I'd rather have a somewhat 'cleaner' way of deleting the mapping data.

That said I think it'd be very nice to have something like these 'add_to_XXX' (in this case:  'add_to_mapped_groups') methods for removing the mapping information like:

   $user -> delete_from_mapped_groups(@group_objects_to_be_deleted);

I believe that CDBI should have all the necessary information internally to be able to dynamically create something like this? Correct me if I am wrong (I have not had a detailed look at the CDBI's internals).


Bill's code:

>BTW -- I'm not that clear on what the "mapping" array can hold -- seem
>like it's always a one element array.

>        # here, $class is "User" and $field->name is "roles"
>        $meta = $class->meta_info('has_many')->{$field->name};
>        # Grab the value from the form field
>        my $value = $field->value;
>        # Figure out which values to keep:
>        my %keep = map { $_ => 1 } ref $value ? @$value : ( $value );
>        # Get foreign class and its key that points to $class
>        my $foreign_class = $meta->foreign_class;  # Mapping class
>        my $foreign_key   = $meta->args->{foreign_key};
>        my $related_key   = $meta->args->{mapping}->[0];
>        # This limits to using a mapping table.  Hard to imagine an interface 
>        # for adding a has_many without a mapping table, but it could be a table
>        # of just columns id, name, f_key, I suppose.
>        die "Failed to find related_key for field [$field] in class [$class]" unless $related_key;
>        # Delete any items that are not to be kept
>        for ( $foreign_class->search( { $foreign_key => $item } ) ) {
>            $_->delete unless delete $keep{ $_->$related_key };
>        }
>        # Add in new ones
>        $foreign_class->create( {
>            $foreign_key => $item,
>            $related_key => $_,
>        } ) for keys %keep;

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digitalcraftsmen.net/mailman/private/classdbi/attachments/20051027/70041293/attachment.htm

More information about the ClassDBI mailing list