Trying to access sphinx internal data

I have a table for countries (model listed below). I am adding thinking sphinx as a search and would like to use it to get the results displayed.

country.rb

class Country < ActiveRecord::Base
  has_many  :provinces
  has_many  :cities
  has_many  :zones
  has_many  :users
  attr_accessible :alpha_2, :alpha_3, :country_name, :numeric, :country_active

  scope :ordered, order("country_name")
  scope :active, where(:country_active => true)

end

country_index.rb

ThinkingSphinx::Index.define :country, :with => :active_record do
  indexes country_name, :sortable => true

  has created_at, updated_at
  has "(select COUNT(provinces.id) from provinces where provinces.country_id = id)", :as => :province_count, :type => :integer
end

In my view I need to add a conditional link to the provinces which belong to a certain country if the province count for that country is greater than 0.

count = country.provinces.count
if count > 0
    link_to(country.country_name, provinces_path(:id => country.id))
else
    country.country_name
end

I tried to replace the active record query for count with

count = Country.search(:select => "province_count", :with => {:country_name => country.country_name})

but am not yet successful in getting this to work yet. How can this be achieved. I was working off of this link

Solution

Two things to note that should help solve this problem:

Firstly, you can force the join on associations by using the join method in an index definition - this saves on the need for a full subquery:

ThinkingSphinx::Index.define :country, :with => :active_record do
  indexes country_name, :sortable => true

  has created_at, updated_at
  has "COUNT(provinces.id)", :as => :province_count, :type => :integer

  join provinces
end

Secondly, and more importantly, if you wish to access Sphinx attributes when using search results, you need to use the Thinking Sphinx pane for that purpose:

search = Country.search(:with => {:sphinx_internal_id => country.id})
search.context[:panes] << ThinkingSphinx::Panes::AttributesPane
count = search.first.sphinx_attributes['province_count']

You'll note that I'm filtering by primary key instead of country name - the id is more specific, so you end up with specific matches, and also, the country name is a field, not an attribute, so if you want to filter by fields, use :conditions instead of :with. And if it was an attribute, you can't filter by it, as Sphinx doesn't support filters on string attributes.

Please note that copying and pasting these three lines into a Rails console will not work, because the console not only evaluates lines, but outputs the result, and outputting a search result invokes a call to Sphinx - and thus the panes don't get applied appropriately. A workaround is to include ; '' at the end of the first line, so the result that gets output is an empty string:

search = Country.search(:with => {:sphinx_internal_id => country.id}); ''
search.context[:panes] << ThinkingSphinx::Panes::AttributesPane
count = search.first.sphinx_attributes['province_count']

If you are actually performing a broad search, not just on a specific country, and you want the provinces count for each country, you can stop reading here. Remove the filter, but make sure you add the pane, and you're good to go.

But, if you're actually running this on just a single country record....

You can simplify things further - you just want the count, after all, not the actual Country object:

search = Country.search(
  :with       => {:sphinx_internal_id => country.id},
  :middleware => ThinkingSphinx::Middlewares::RAW_ONLY
)
search.first['province_count']

But really, if you already have the country object, then running a search for the sole purpose of getting the number of provinces feels like overkill to me. Either just call country.provinces.count, or use ActiveRecord's counter_cache option and then have the column provinces_count on your Country model - this would undoubtedly be the fastest option in the long run.

(Sorry, this answer ended up being far longer than I expected - but it covers off a few different avenues.)