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.catal...@gmail.com mailto:learn.catal...@gmail.com wrote:
On Thu, Jul 12, 2012 at 9:04 AM, fREW Schmidt fri...@gmail.com
mailto:fri...@gmail.com wrote:
On Thu, Jul 12, 2012 at 2:48 AM, Patrick Meidl
patr...@pantheon.at mailto:patr...@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;