Thinking sphinx - index for join with condition (has_and_belongs_to_many)

Question

I have the model Service, it has the scope to filter services by service type ids:

class Service < ActiveRecord::Base
  has_and_belongs_to_many :service_types  

  scope :by_service_types, -> ids { joins(:service_types).where('service_types_services.service_type_id in (?)', ids) }
end

class ServiceType < ActiveRecord::Base
  has_and_belongs_to_many :services
end

So, when I run scope, I get such result:

Service.by_service_types([54])
  Service Load (0.8ms)  SELECT "services".* FROM "services" INNER JOIN "service_types_services" ON "service_types_services"."service_id" = "services"."id" INNER JOIN "service_types" ON "service_types"."id" = "service_types_services"."service_type_id" WHERE "services"."deleted" = 'f' AND (service_types_services.service_type_id in (54))
 => ...

How can I build the attribute to build the index for such scope?

Answer 1

Firstly, you'll want service type ids as a multi-value attribute in your Service index definition:

ThinkingSphinx::Index.define(:service, :with => :active_record) do
  # ... existing index definition

  has service_types.id, :as => :service_type_ids
end

And then you can search and use that attribute:

Service.search(:with => {:service_type_ids => 54})

If you want to wrap that into something like an ActiveRecord scope, Thinking Sphinx has its own scope functionality (the two cannot be combined, as ActiveRecord works with databases querying with SQL, but Sphinx scopes work with Sphinx querying with SphinxQL - similar, but not quite the same).

# include this in your model:
include ThinkingSphinx::Scopes

sphinx_scope(:search_by_service_type) { |ids|
  {:with => {:service_type_ids => ids}}
}

And then searching:

Service.search_by_service_type(54)
# You can chain further search arguments onto the scope:
Service.search_by_service_type(54).search('foo', :order => 'created_at DESC')