I’d like some adviceif possile
i have 2 models Location and Listing Location has many Listings Listing
belongs to location. Using teh acts_as_geocodable plugin i have
geocoded the Location model.
this allows me to do a find like so
location = Location.find(:all, :origin => postal_code, :order =>
“distance asc”)
i would like to include the child objects in the result so i can show
listings sorted by distance.
i posted at railsforum.com and Duplex suggested using :include as
follows :-
location = Location.find :all, :origin => postal_code,
:order => “distance asc”,
:include => :listings #eager-loading for
performance
:conditions => “state = ‘published’”
i get mysql error when i try that pasted here to save space
http://pastie.caboo.se/157933
This is my first project so I’m not sure but i think maybe its unable to
do the :include because distance is a generated field or maybe it’s
because using :origin already is a type of JOIN query. any ideas? I’m
thinking maybe the way round this is to find a way to do the sql query
directly and bypass rails?
any suggestions welcome
cheers
Rigagoogoo
Hi Nathan,
The generated SQL does not contain a “distance” field yet you are
trying to order based on that field (:order => “distance asc”). Does
your Location model have this field? If not, then I would suggest you
remove the :order option and then try again. I don’t understand why
this didn’t fail in your first example without the :include option
though 
Regards,
Jabbslad
On Feb 27, 1:13 am, Nathan S. [email protected]
Hi Jabbslad
thanks for such a quick response 
Location model doesn’t have distance field, it should be generated
by the inclusion of :origin in the find method indeed if i do the
following
location = Location.find :all, :origin => “some_postcode”, :order =>
“distance asc”
it works fine and translates into an SQL query that gets mysql to
determine the distance between the Locations longitude and latitude
and the long/lat of the provided postcode - heres the SQL it creates
http://pastie.caboo.se/157973
it fails when i try to combine it with :include. so given :include
doesn’t play ball with extended find acts_as_geocodable provides how
do i get to the endpoint i need
given Location has many Listings display Listings in order of
distance from users postcode my options as i see it are:-
- Iterate through the results and create a new array but that hits
the database for each location.
location= Location.find :all, :origin => “some_postcode”, :order =>
“distance asc”
-
maybe better to use
locations= Location.find :all, :origin => “some_postcode”, :order =>
“distance asc”
listings = Listing.find :all
and some ruby to combine them into one array?
-
Use the locations result to build a new sql query?
-
fix the problem with :origin and include - probably not beginner
territory 
frankly I don’t know how to do any of the above so all of those
options are daunting so any advice or examples would be great.
cheers
Rigagoogoo
On 27 Feb 2008, at 03:47, nathan sharkey wrote:
“distance asc”
it works fine and translates into an SQL query that gets mysql to
determine the distance between the Locations longitude and latitude
and the long/lat of the provided postcode - heres the SQL it creates
Parked at Loopia
I imagine that origin’s magic uses the :select option to include that
extra computed column. Unfortunately eager loading overwrites
that :select with its own and so that’s not going o play ball.
Personally I’d go with a varian on 2:
locations= Location.find :all, :origin => “some_postcode”, :order =>
“distance asc”
all_listings = Listing.find :all, :conditions => [‘location_id in
(?)’, locations] #pre rails 2 i think that needs to be changed to
locations.map(&:id)
listings_hash = all_listings.inject({}) do |memo, listing|
listings_for_location = (memo[listing.location_id] ||= [])
listings_for_location << listing
memo
end
listings_hash is now a hash with all your listings, so if you want to
know all the listings for location[2], then you just do
locations[listings[2].id]
or cut out the middle man and do
location_map = {}
locations.each do |location|
location.listings.loaded
location_map[location.id] = location
end
all_listings.each do |listing|
parent = location_map[listing.location_id]
association_proxy = parent.listings
association_proxy.loaded
association_proxy.target.push(listing)
end
which sets up the association magic so that you can just do
location.listings
Fred
Hi fred thanks for the examples they’ve opened my eyes to the .include
() method after some playing with your exaple by using p to print out
the variables as i ent in console I think I understand it and will
probably be able to cobble together what i need using it but the
second example throws me with .loaded i’ve googled it but come up
with nothing? i’ve searched a rails project using textmate and not
found it as a method and searched the ruby docs. can you advise what
it does?
cheers
Nathan
On 27 Feb 2008, at 12:54, nathan sharkey wrote:
Hi fred thanks for the examples they’ve opened my eyes to the .include
() method after some playing with your exaple by using p to print out
the variables as i ent in console I think I understand it and will
probably be able to cobble together what i need using it but the
second example throws me with .loaded i’ve googled it but come up
with nothing? i’ve searched a rails project using textmate and not
found it as a method and searched the ruby docs. can you advise what
it does?
The key bit of information here is that location.listings isn’t an
array (even though it looks like one). It’s an association proxy.
That’s why you can do things like location.listings.find and so on.
When you try and do something to the association proxy that requires
the actual elements (eg if you try to access location.listings.first)
then the listings are actually loaded. What we’re doing here is
prefetching all the listings and stuffing them into where the
association proxy stores the actual array (target). The call to loaded
means that the proxy will think that it has already loaded the array.
This sort of stuff isn’t really documented much, but you can work it
out for yourself by reading the relevant bits of the rails source
(associations.rb, association_proxy.rb etc…)
Fred
Thanks Fred for the explanation i think i understand the intention
just not the how yet. i have only just learn’t the meaning of
include and inject methods. 
i think i am spending too much time worrying about calls to my sql
there is no point spending days trying to understand this for my
first app. It could be years before i got enough traffic to it for
it to really make a difference. All I want is to create a view with
all Listings ordered by reletive distance from users postcode. The
method can be tuned at a later date if it ever needs to be I’ve
already spent 2 days trying to understand…
so what’s easiest (not necessarily best) way for me to do this?
using acts_as_geocodable I can get relative distance to postcode in
the three following ways
locations = Locations.find(:all, :origin => postcode, :order =>
“distance”)
locations[0].distance
location = Location.find(0, :origin => postcode)
location.distance
******the above .distance method is only available for the result set
not for the model in general also it can’t be combined with an
include *******
Listing.find(1).location.distance_to postcode
ignoring how many sql queries are required how do i create a view
that works?
so something like :-
listings = Listing.find_all_by_state(“published”) # returns all my
published listings
for each listing in listings do
puts listing.name &" “& listing.intro &” “&
listing.location.distance_to(“postcode”) &” "& "link to"listing.url
end
but ordered by distance and usable in a view not just the console???
thanks again
Nathan