Defining indexes for associated models with Thinking Sphinx

Question

What is the proper way of defining indexes on associated models with following configuration?

I have model Locality with lat and lng attributes and associated models Profile and User

class User < ActiveRecord::Base
  has_one :user_profile

  define_index do
    # This doesn't work :(
    has "RADIANS(user_profiles.localities.lat)", :as => :lat, :type => :float
    has "RADIANS(user_profiles.localities.lng)", :as => :lng, :type => :float
  end
end

end

class UserProfile < ActiveRecord::Base
  belongs_to :user
  belongs_to :locality
end

class Locality < ActiveRecord::Base
  has_many :user_profiles
end

I need to define indexes for User model so I can perform geo-searches on it.

Thank you for the answers!

score 10 · Accepted Answer · answered Feb 14 '11 at 21:25

The problem is twofold:

Thinking Sphinx doesn't know that you want to join the user_profile and locality associations.
SQL snippets should be just that - standard SQL - so you can't chain associations within them.

The following should do the trick:

define_index do
  # force the join on the associations
  join user_profile.locality

  # normal SQL:
  has "RADIANS(localities.lat)", :as => :lat, :type => :float
  has "RADIANS(localities.lng)", :as => :lng, :type => :float
end

Claudio's point of using singular references within the SQL is not correct - within SQL, you want table names. But you do want the correct association references in the join call (hence they're singular there).

Cheers

Great, sorry for the confusion that I've created :) – Claudio Acciaresi Feb 14 '11 at 23:59 — Claudio Acciaresi, Feb 14 '11 at 23:59

score 1 · Answer 2 · answered Feb 14 '11 at 20:26

Don't really know the exact solution but:

You have a typo:

has_one :user_profile

define_index do
    # This doesn't work :(
    has "RADIANS(user_profiles.localities.lat)", :as => :lat, :type => :float

You are using "user_profiles" for the attribute, it should not be plural, try changing it to: "user_profile".

I repeat, I don't know if you can navigate through associations for this case, but if you can, this should be a typo.

You can read here for more information: http://freelancing-god.github.com/ts/en/geosearching.html

I was trying to to reference table names. Pat's answer solves the problem, thanks anyway! — Igor Pavelek, Feb 15 '11 at 09:21

Defining indexes for associated models with Thinking Sphinx

2 Answers2