those two are a great mix ! in here -> http://notch8.com/articles/2007/10/15/sphinx-and-ultrasphinx-and-eye-on-search get some clues on how to compile models associated to each others (search a service and its provider); but doesn't seem to work with h_m_p, thanks for any clues (might have a lack of understnding here).
It seems, UltraSphinx has some minor trouble getting the SQL for more complex queries right.
I've used it like this:
is_indexed :fields => [:name, :description, :updated_at, :created_at, :orders_count, :views, :low_res, :category_id], :concatenate => [{:association_name => "tags", :field => "name", :as => "tag_name", :association_sql => "LEFT JOIN taggings ON (taggings.taggable_id=products.id AND taggings.taggable_type='Product') LEFT JOIN tags ON taggings.tag_id=tags.id"}], :include => [{:association_name => "member", :field => "rating", :as => "member_rating", :association_sql => "LEFT JOIN members ON members.id=products.member_id LEFT JOIN ratings ON (ratings.rateable_id=members.id AND ratings.rateable_type='Member')"}], :conditions => "status = 1", :delta => {:field => "updated_at"}
The first index, concatenating all tags of acts_as_taggable_on_steroids worked out of the box. Then I added the second one to get the acts_as_rateable data in and allow for sorting by ratings This one failed to generate the correct SQL out of the belongs_to relationship of the rating. (It used the singular association name "member" as a table name instead of members Maybe it's my fault and I got some syntax detail wrong, but i tried several variatons, all failed.
So at the end I took the generated sphinx config file and corrected the SQL. Which means of course, that in future I've to be careful about running us:config. But still, all the rest of the generated config saves a lot of work.
I had read this one, http://rubyforge.org/forum/forum.php?thread_id=18082&forum_id=14244; steroids seems to work well…
I found some clues here : /usr/lib/ruby/gems/1.8/gems/has_many_polymorphs-2.12/test/integration/app/config/ultrasphinx/development.conf.canonical
wich show the output :
SELECT (states.id * 4 + 0) AS id, CAST(GROUP_CONCAT(addresses.name SEPARATOR ' ') AS CHAR) AS address_name, 0 AS capitalization, 'Geo::State' AS class, 0 AS class_id, '' AS company, '' AS company_name, 0 AS company_name_facet, '' AS content, UNIX_TIMESTAMP('1970-01-01 00:00:00') AS created_at, 0 AS deleted, '' AS email, '__empty_searchable__' AS empty_searchable, '' AS login, '' AS name, '' AS state, 0 AS user_id FROM states LEFT OUTER JOIN addresses ON states.id = addresses.state_id WHERE states.id >= $start AND states.id <= $end GROUP BY id;
seems to show that it would work, I just like to get the source from the concerned model… might take time on this one. The problem is, when you see hmp working in the console, you get this kind of sql syntax (the one above); wich is basically unwriteable if you have more than three models working with it, this syntax is totally different from the one you would just write in a regular sql syntax to call records as they are stored : SELECT model1.* FROM model1 INNER JOIN model1.id = assoc_model.assoc_field_id WHERE blah…