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
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.)