[Dbix-class] many_to_many data access

[Patrick Meidl]

On Wed, Jul 11 2012, Robyn Jonahs <learn.catalyst at gmail.com> wrote:

> I was trying to get some many_to_many data into a FormFU select
> element but I am having trouble. I think that it is time to ask the
> pros for help.
> 
> I was staying similar to the online tutorial. In my add and edit sub
> routines, I have the following code (similar to the online tutorial)
> 
>         # Stores
>         my @store_objs = $c->model("DB::Store")->all();
>         my @stores;
>         foreach (sort {$a->store cmp $b->store} @store_objs) {
>         #  push(@stores, [$_->id, $_->store]);
>           push(@stores, [$_->id, $_->full_address, $_->store]);
>         }
>         my $select3 = $form->get_element({name => 'stores'});
>         $select3->options(\@stores);
> 
> So I want to get a list of all the values in a table called stores and
> stick them into a select element in FormFU. I can do this fine. What I
> wanted to do was to give it the full address of the store bringing in
> the state, city and country from many_to_many relationships with these
> tables.  I was able to do this in another test application but the
> relationships were has_many not many_to_many. I am stumped on how to
> do this.
> 
> The store model is simple
> 
> __PACKAGE__->add_columns(
>   "id",
>   { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
>   "store",
>   { data_type => "varchar", is_nullable => 0, size => 255 },
>   "notes",
>   { data_type => "varchar", is_nullable => 1, size => 255 },
> );
> 
> and it contains
> 
> __PACKAGE__->has_many(
>   "store_cities",
>   "ProductTracker::Schema::Result::StoreCity",
>   { "foreign.store_id" => "self.id" },
>   { cascade_copy => 0, cascade_delete => 0 },
> );
>  ...
> 
> __PACKAGE__->many_to_many("cities", "store_cities", "city");
> 
> And I tried to do this...
> #
> # Row-level helper methods
> #
> =head2 full_address experimental
> 
>   Get the city, state and country that correspond to a specific Store ID
> 
> =cut
> sub full_address {
>     my ($self) = @_;
> 
>     my $cities = $self->store_cities;
>     return $self->store . ' ' . $cities->city->city;
> 
> #    return $self->store . ' ' . $self->city->city . ', ' .
> $self->states->abb . ' ' . $self->countries->abb;
> }

there are several errors in this method.

first, to get the cities associated to the store, use the many_to_many
relationship you defined; if you have such a relationship, you usually
never use the bridging table (store_cities in your example) directly.

second, since you are dealing with a many_to_many, there might be
multiple cities associated to a store, you have have to deal with this.
so your code should look something like this:

--8<---------------------------------------------------------------

sub full_address {
  my ($self) = @_;

  my $result = $self->store;

  my @cities = $self->cities;
  foreach my $city (@cities) {
    $result .= sprintf(' | %s, %s %s', $city->city,
      $self->states->abb, $self->countries->abb);
  }
  
  return $result;
}

--8<---------------------------------------------------------------

not sure about the calls to states and countries, you didn't include
the code what these methods return; look like many_to_many to me too, or
rather belongs_to relations on City? so I'm speculating that your code
will look like

    $result .= sprintf(' | %s, %s %s', $city->city,
      $city->state->abb, $city->country->abb);
  
HTH

    patrick

-- 
Patrick Meidl ........................ patrick at pantheon.at
Vienna, Austria ...................... http://gplus.to/pmeidl