[Dbix-class] many_to_many data access

[Frank Schwach]

Given the purpose of the app you are writing, it seems to me that what 
you really want is the ability to compare prices within a certain area, 
whatever that may be, as in " give me the lowest price for this item 
within a 50mile radius from my home" - is that a fair assumption?
If so, here is an alternative to re-building Google's map databases:

Use Google's APIs to build an query your database with geo coordinates. 
Check out these resources:

https://developers.google.com/places/documentation/autocomplete
https://developers.google.com/maps/articles/phpsqlsearch_v3

you can use the Google API to let users enter the correct address (with 
auto-completions to ensure "normality") and your database just stores 
the longitude and latitude of the place. Now you can do the range 
queries against your local database as described in the developers guide.

Would that be an option for you?




On 12/07/12 19:23, Robyn Jonahs wrote:
> On Thu, Jul 12, 2012 at 1:24 PM, Robyn Jonahs 
> <learn.catalyst at gmail.com <mailto:learn.catalyst at gmail.com>> wrote:
>
>     On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt <frioux at gmail.com
>     <mailto:frioux at gmail.com>> wrote:
>
>
>
>         On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl
>         <patrick at pantheon.at <mailto:patrick at pantheon.at>> wrote:
>
>             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 <http://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) = @_;
>
>     my $result = $self->store;
>
>     my @cities = $self->cities;
>     foreach my $city (@cities) {
>        $result .= ' :|: ' . $city->city;
>     }
>     # Just take the first element of the array?
>     my @states = $self->states;
>     #$result .= ', ' . @states[0]->state; # Use the short form
>     $result .= ', ' . @states[0]->abb;
>
>     my @countries = $self->countries;
>     $result .= ' ' . @countries[0]->abb;
>
>     return $result;
> }
>
> thanks to everyone.
>
>
> _______________________________________________
> 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


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.