# Ultrasphinx and has\_many\_polymorphs

**URL:** <https://www.ruby-forum.com/t/ultrasphinx-and-has-many-polymorphs/148260>\
**Category:** Rails\
**Created:** [September 11, 2008, 1:24am UTC](https://www.ruby-forum.com/t/ultrasphinx-and-has-many-polymorphs/148260 "2008-09-11T01:24:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![bbnnt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@bbnnt](https://www.ruby-forum.com/u/bbnnt)\
**Post date:** [September 11, 2008, 1:24am UTC](https://www.ruby-forum.com/t/ultrasphinx-and-has-many-polymorphs/148260/1 "2008-09-11T01:24:11Z")

</div>

those two are a great mix !  
in here →

> **[SoftServ | Services](https://softserv.scientist.com/)**
>
> SoftServ is a software development consultancy that turns software ideas into digital realities.

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

---

<div class="post-metadata">

**Author:** ![bbnnt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@bbnnt](https://www.ruby-forum.com/u/bbnnt)\
**Post date:** [September 11, 2008, 10:42am UTC](https://www.ruby-forum.com/t/ultrasphinx-and-has-many-polymorphs/148260/2 "2008-09-11T10:42:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bbnnt](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@bbnnt](https://www.ruby-forum.com/u/bbnnt)\
**Post date:** [September 11, 2008, 11:14am UTC](https://www.ruby-forum.com/t/ultrasphinx-and-has-many-polymorphs/148260/3 "2008-09-11T11:14:43Z")

</div>

I had read this one,  
[http://rubyforge.org/forum/forum.php?thread\_id=18082&forum\_id=14244](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…