those two are a great mix !
in here →
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…