ruby on rails - Searching with thinking_sphinx and filtering results -

i have scenario thought pretty basic, found out can't achieve need. why have question thinking_sphinx's expert.

the scenario this: need search within list of companies , return has address (there can many address company) belongs particular city or none @ (this can do).

i have following models :

class company < activerecord::base     has_many :company_addresses      define_index       indexes :name       indexes :description       indexes :keywords     end end 

and

class companyaddress < activerecord::base end 

the companyaddress has city_id property. without looping through returned records sphinx search, there way achieve same thing more easily?

i'm using rails 3.0.3 , thinking_sphinx.

you'll want add attribute pointing city_id values company:

has company_addresses.city_id, :as => :city_ids 

and can filter on companies belonging specific city:

company.search 'foo', :with => {:city_ids => @city.id} 

if want both matching specific city or has no cities, that's little trickier, or logic attribute filters more little tricky @ best. ideally want single attribute contains either 0, or city ids. doing depends on database, mysql , postgres functions vary.

as rough idea, though - might work in mysql:

has "if(count(city_id) = 0, '0', group_concat(city_id separator ',')",   :as => :city_ids, :type => :multi 

postgres reasonably similar, though may need use case statement instead of if, , you'll want use couple of functions group concatenation:

array_to_string(array_accum(city_id, '0')), ',') 

(array_accum provided thinking sphinx, there no direct equivalent of group_concat in postgresql).

anyway, if need approach, , sql figured out, query looks like:

company.search 'foo', :with => {:city_ids => [0, @city.id]} 

this match on either 0 (representing no cities), or specific city.

finally: if don't reference company_addresses association anywhere in normal fields , attributes, you'll need force join in define_index:

join company_addresses 

hopefully provides enough clues - feel free continue discussion here or on the google group.