Join Multiple Copies of Same Model in Thinking Sphinx Index

This was a vexing problem that probably affects 0.1% of all Thinking Sphinx users, but for those select few, you can benefit from my pain.

We have a model with the following associations:

has_many :merch_match_data_tags, :class_name => "MerchMatchItemData", :dependent => :delete_all
has_one :mm_bizarre, :class_name => "MerchMatchItemData", :conditions => { :data_tag_id => MerchMatchItemData::BIZARRE }
has_one :mm_good_picture, :class_name => "MerchMatchItemData", :conditions => { :data_tag_id => MerchMatchItemData::NICE_PICTURE }
has_one :mm_funny, :class_name => "MerchMatchItemData", :conditions => { :data_tag_id => MerchMatchItemData::FUNNY }

Intending to add these to a Sphinx index, we used the following code:

has mm_good_picture.tag_count, :as => :good_picture_points
has mm_bizarre.tag_count, :as => :bizarre_points
has mm_funny.tag_count, :as => :funny_points

What perplexed me after trying this was that while the “good_picture_points” could be queried and sorted, bizarre_points and funny_points returned no Sphinx results. Looking into the output generated by thinking_sphinx:configure, I discovered why:

...
LEFT OUTER JOIN `merch_match_item_datas` ON merch_match_item_datas.item_id = items.id AND `merch_match_item_datas`.`data_tag_id` = 0   LEFT OUTER JOIN `merch_match_item_datas` mm_bizarres_items ON mm_bizarres_items.item_id = items.id AND `merch_match_item_datas`.`data_tag_id` = 2   LEFT OUTER JOIN `merch_match_item_datas` mm_good_values_items ON mm_good_values_items.item_id = items.id AND `merch_match_item_datas`.`data_tag_id` = 7   LEFT OUTER JOIN `merch_match_item_datas` mm_funnies_items ON mm_funnies_items.item_id = items.id AND `merch_match_item_datas`.`data_tag_id` = 4  
...

The problem was that, in determining the SQL to build, Thinking Sphinx uses the first association it comes across as the default set of conditions for all future joins to the table. So, in this case, anything that joined the merch_match_item_datas table was going to be joining that table with the data_tag_id = 0 condition of our first declared association (mm_good_picture_tag). That is, mm_bizarre now was looking for data_tag_id=0 and data_tag_id=[id of bizarre tag]. So, nothing was returned.

After a bit of head scratching, I came up with the following workaround for this:

has merch_match_data_tags.tag_count
has mm_good_picture.tag_count, :as => :good_picture_points
has mm_bizarre.tag_count, :as => :bizarre_points
has mm_good_value.tag_count, :as => :good_value_points

Basically, just make the first association that Thinking Sphinx encounter be an unqualified, unfiltered association to the merch_match_data_table. This ensures that the proper join structure is setup, so all of the subsequent has attributes function as they should.

Hope that I’m not the only one ever to find this useful.