[Dbix-class] many_to_many data access

[Robyn Jonahs]

Hi,

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 =3D $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 =3D $form->get_element({name =3D> '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 =3D> "integer", is_auto_increment =3D> 1, is_nullable =3D> 0 =
},
  "store",
  { data_type =3D> "varchar", is_nullable =3D> 0, size =3D> 255 },
  "notes",
  { data_type =3D> "varchar", is_nullable =3D> 1, size =3D> 255 },
);

and it contains

__PACKAGE__->has_many(
  "store_cities",
  "ProductTracker::Schema::Result::StoreCity",
  { "foreign.store_id" =3D> "self.id" },
  { cascade_copy =3D> 0, cascade_delete =3D> 0 },
);
 ...

__PACKAGE__->many_to_many("cities", "store_cities", "city");

And I tried to do this...
#
# Row-level helper methods
#
=3Dhead2 full_address experimental

  Get the city, state and country that correspond to a specific Store ID

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

    my $cities =3D $self->store_cities;
    return $self->store . ' ' . $cities->city->city;

#    return $self->store . ' ' . $self->city->city . ', ' .
$self->states->abb . ' ' . $self->countries->abb;
}


The Schema/Result/City.pm structure is simple too

__PACKAGE__->add_columns(
  "id",
  { data_type =3D> "integer", is_auto_increment =3D> 1, is_nullable =3D> 0 =
},
  "city",
  { data_type =3D> "varchar", is_nullable =3D> 0, size =3D> 255 },
  "notes",
  { data_type =3D> "varchar", is_nullable =3D> 1, size =3D> 255 },
);

But nothing I am trying works. I am looping over the resultset in the main
call... so I should be able to do row level queries I think from the
Schema/Result/Store.pm and get the value (yes probably bad Database design
in hindsight) through the many_to_many relationship to the associated data
in the Schema/Result/City.pm Country.pm and State.pm

Can anyone help me? Do I need to give more information or is it clear that
I am violating something? If I can figure out how to get the data in cities
or store_city then I should be able to get others.

Thanks I have been scratching my head and reading for over a week on this
now trying to learn.


I was able to do this in my other application:

__PACKAGE__->belongs_to(
  "state",
  "LT::Schema::Result::State",
  { id =3D> "state_id" },
  { is_deferrable =3D> 1, on_delete =3D> "CASCADE", on_update =3D> "CASCADE=
" },
);

#
# Row-level helper methods
#
sub full_address {
    my ($self) =3D @_;

    return $self->vendor . ' ' . $self->address . ', ' . $self->state->abb
. ' ' . $self->country->abb;
}

where state and country are belongs_to relationships
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120711/d60=
22a11/attachment-0001.htm