[Dbix-class] many_to_many data access

Robyn Jonahs learn.catalyst at gmail.com
Thu Jul 12 18:23:12 GMT 2012


On Thu, Jul 12, 2012 at 1:24 PM, Robyn Jonahs <learn.catalyst at gmail.com>wro=
te:

> On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt <frioux at gmail.com> wrote:
>
>>
>>
>> On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl <patrick at pantheon.at>wrot=
e:
>>
>>> 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.
>>>
>>
>> I disagree.  The use of data other than left_id and right_id is what
>> makes many_to_many jointables so awesome.  The obvious data to put in the
>> join table is the date the intermediate was created, or often which user
>> added it.  Once I created a DB that was supposed to represent judges in =
the
>> US.  It had tables for Military Rank and Military Service and then a join
>> table that joined the judge to those two and it had start dates and end
>> dates etc.  That db had a total of 14~ join tables, nearly all of which =
had
>> intermediate data.  It's a very handy and powerful pattern.
>>
>> --
>> fREW Schmidt
>> http://blog.afoolishmanifesto.com
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>>
>
> Conceptually I have worked myself into a confused state. Originally I
> thought that the many_to_many relationships were the proper way to go. But
> the problem is that the locations have to be unique. I did not generate
> that. My problem is that I am not making unique addresses. I think that I
> need a way to join the data record of interest (FOO) to a combination of
> store, city, state and country. So it seems that I need a join table
> between FOO_id and the set of (store_id, city_id, state_id, country_id).
> This seems wrong to me.
>
> The alternate if I insist on many_to_many tables is to have a Location
> table that has records for unique combinations of those four things.
>
> I was working under the concept that if any data entries were repeated in
> database, you should put them into a separate table. It may be that I need
> to use a simple has_one and has_many relationship to have a unique address
> record. I can't see how to make the many_to_many work at the moment in my
> mind.
>
> I think that I have screwed up the database logic but I have confused
> myself so much now that I could use help. Have I made it too complicated?=
 I
> guess a pitch back to the Best practice for dealing with addresses.
>
>
CONCLUSION to original issue:
Thanks to everyone for the help.

1. I did screw up by using the many_to_many relationships. In my html and
data flow, I restricted it as if it were a has_one and a has_many
relationship to cities, countries and states. Thus Each "store" is forced
to be a unique row in my database. I will fix this to the proper
relationships and follow it through my application to do it as I intended.
I will keep in mind the maintenance issues if this were a multi-user
application and people could screw it up.

2. Since each store has one associated city... the many to many only has
one result. My temporary patch so I can enter data is as follows.

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

    my $result =3D $self->store;

    my @cities =3D $self->cities;
    foreach my $city (@cities) {
       $result .=3D ' :|: ' . $city->city;
    }
    # Just take the first element of the array?
    my @states =3D $self->states;
    #$result .=3D ', ' . @states[0]->state; # Use the short form
    $result .=3D ', ' . @states[0]->abb;

    my @countries =3D $self->countries;
    $result .=3D ' ' . @countries[0]->abb;

    return $result;
}

thanks to everyone.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120712/0b7=
2d65e/attachment.htm


More information about the DBIx-Class mailing list