Searching with thinking_sphinx and filtering results

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.

Answers


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.


Need Your Help

How to add values in a multi map

java multimap

I can't seem to figure this out even after look at this: Adding values of two maps whenever there is a key match. I made a multi map that contains radius as keys and the amplitude as values. But th...

Web application using Ajax with Java based server- Security related

java ajax security xss

I have a web application which uses Ajax for several tasks. It has Java as it's middleware (Struts 1.x)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.