Association Problem due to Parent Latitude/Longitude

[djfobbz]

I have a listing model that has_many statuses:

class Listing < ActiveRecord::Base
has_many :statuses

define_index do
indexes :name
indexes :description
indexes [:address, :city, :state, :zip], :as => :full_address
has statuses(:id), :as => :status_ids, :type => :integer
has "RADIANS(latitude)", :as => :latitude, :type => :float
has "RADIANS(longitude)", :as => :longitude, :type => :float
set_property(:morphology => 'stem_en')
set_property(:ignore_chars => 'U+0027')
set_property(:enable_star => true)
end
end

My problem is that I get the following error while creating/rebuilding
the index:

indexing index 'listing_core'...
ERROR: index 'listing_core': sql_range_query: Column 'latitude' in
field list is ambiguous (DSN=mysql://root:***@localhost:3306/
listings_development).

Secondly, my question is that even if I get this to work, can I order
the results based on the associations count/size and then by distance
and relevance?

The index is created just fine when I remove:

has statuses(:id), :as => :status_ids, :type => :integer

What could be going on?

[Pat Allan]

It looks like some other table that you're joining on has lat/lng columns... that'd be statuses.

The fix should be easy enough - you just need to explicitly refer to the listings table.

has "RADIANS(listings.latitude)", :as => :latitude, :type => :float
has "RADIANS(listings.longitude)", :as => :longitude, :type => :float

As for sorting by associations count, you need that count as an attribute, probably done like so (using statuses as an example):

has statuses(:id), :as => :status_ids

has "COUNT(statuses.id)", :as => :status_count, :type => :integer

You need the first line to force the join to the statuses table. Also, in your case, you had an explicit type - that's not necessary.

And then for sorting, with status count before relevance, before weighting:

Listing.search 'foo',
:geo => [@lat, @lng],
:order => "status_count DESC, @geodist ASC, @weight DESC"

Hope this helps.

--
Pat

> --
>
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
>
>

[djfobbz]

Pat,

Thank you for your prompt reply. I followed your steps and was able
successfully able to re-create the index. However, on output, the
results were still ordered by DISTANCE as opposed to STATUS_COUNT

here's my search code:

Listing.search(params[:q], :include =>
[:category_1, :category_2, :category_3, :category_4, :category_5, :category_6], :geo
=> [@lat,@lng], :order => "status_count DESC, @geodist ASC, @weight
DESC", :page => params[:page])

and here's my output code:

- @search_listings.each do |l|
%tr
%td= "#{l.dts_count_today?} | #{link_to 'DT',
new_listing_status_path(l)}"
%td= link_to l.name, listing_path(l)
%td= h l.city_state
%td= l.type?
%th= meters_to_miles(l.sphinx_attributes['@geodist'])

I wonder what might be wrong?

[djfobbz]

never mind...my fault...looking at code during a time when I'd rather
be enjoying my vacation can sometimes be disastrous! i was in the
wrong loop! :)