Searching with thinking_sphinx and filtering resul

Question

I have this scenario where I thought it would be pretty basic, but found out that I can't really achieve what I need. This is why I have this question for a thinking_sphinx's expert.

The scenario is this: I need do a search within a list of companies and only return those who has an address (there can be many address by company) which belongs to a particular city or none at all (this I can do).

I have the 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 a city_id property. Without looping through all returned records from a sphinx search, is there a way to achieve the same thing more easily?

I'm using Rails 3.0.3 and thinking_sphinx.

Answer 1

You'll want to add an attribute pointing to the city_id values for the company:

has company_addresses.city_id, :as => :city_ids

And then you can filter on Companies belonging to a specific city:

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

If you want both matching to a specific city or has no cities, that's a little trickier, as OR logic for attribute filters is more than a little tricky at best. Ideally what you want is a single attribute that contains either 0, or all city ids. Doing this depends on your database, as MySQL and Postgres functions vary.

As a rough idea, though - this might work in MySQL:

has "IF(COUNT(city_id) = 0, '0', GROUP_CONCAT(city_id SEPARATOR ',')",
  :as => :city_ids, :type => :multi

Postgres is reasonably similar, though you may need to use a CASE statement instead of IF, and you'll definitely want to use a couple of functions for the group concatenation:

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

(array_accum is provided by Thinking Sphinx, as there was no direct equivalent of GROUP_CONCAT in PostgreSQL).

Anyway, if you need this approach, and get the SQL all figured out, then your query looks something like:

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

This will match on either 0 (representing no cities), or the specific city.

Finally: if you don't reference the company_addresses association anywhere in your normal fields and attributes, you'll need to force to join in your define_index:

join company_addresses

Hopefully that provides enough clues - feel free to continue the discussion here or on the Google Group.