# How to set up a has\_many :through association with Ultrasphinx

**URL:** <https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933>\
**Category:** Rails\
**Created:** [February 21, 2009, 2:19am UTC](https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933 "2009-02-21T02:19:55Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![adam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4af34b/32.png) [@adam](https://www.ruby-forum.com/u/adam)\
**Post date:** [February 21, 2009, 2:19am UTC](https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933/1 "2009-02-21T02:19:55Z")

</div>

I’m using the Ultrasphinx plugin with Sphinx to add search to my app,  
but I’m having a tough time figuring out how to index a  
has\_many :through relationship. Say, for example, I’m looking at a  
relationship like this:

Playlist has\_many songs :through =\> ‘playlistings’

I want to be able to do a song-based search that will return the  
Playlists that have those songs. I know I need to use  
the :association\_sql key to set up the has\_many :through join, but my  
SQL sauce is weak. Here’s what I have right now, which works only for  
the first song:

```
is_indexed :fields => [{:field => 'name', :as =>

```

‘playlist\_name’}],  
:include =\> [{:association\_name =\> “songs”, :field =\>  
‘song\_name’,  
:association\_sql =\> “JOIN  
(playlistings, songs) ON (playlists.id=playlistings.playlist\_id AND  
playlistings.song\_id=songs.id)”}]

Unfortunately, like I said, if I do a search by song name, Ultrasphinx  
only matches playlists if I search for the first song in a playlist…  
searching by all other song names (e.g., the name of the second or  
third song in a playlist) returns nothing. Any ideas?

Thanks so much for any help!  
Adam

---

<div class="post-metadata">

**Author:** ![adam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4af34b/32.png) [@adam](https://www.ruby-forum.com/u/adam)\
**Post date:** [February 21, 2009, 3:44am UTC](https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933/2 "2009-02-21T03:44:11Z")

</div>

Hey everyone, got it to work! Here’s what I’m using:

is\_indexed :fields =\> [{:field =\> ‘name’, :as =\> ‘playlist\_name’}],  
:concatenate =\> [{ :class\_name =\> ‘Song’, :field =\> ‘name’,  
:association\_sql =\> “LEFT JOIN playlistings ON  
((playlist\_id=playlists.id)) LEFT JOIN songs ON  
(songs.id=playlistings.song\_id)”, :as =\> ‘song’ }]

Hopefully that’ll help someone out sometime. 🙂

---

<div class="post-metadata">

**Author:** ![adam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4af34b/32.png) [@adam](https://www.ruby-forum.com/u/adam)\
**Post date:** [November 2, 2009, 1:23pm UTC](https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933/3 "2009-11-02T13:23:42Z")

</div>

Adam wrote:

> Hey everyone, got it to work! Here’s what I’m using:
> 
> is\_indexed :fields =\> [{:field =\> ‘name’, :as =\> ‘playlist\_name’}],  
> :concatenate =\> [{ :class\_name =\> ‘Song’, :field =\> ‘name’,  
> :association\_sql =\> “LEFT JOIN playlistings ON  
> ((playlist\_id=playlists.id)) LEFT JOIN songs ON  
> (songs.id=playlistings.song\_id)”, :as =\> ‘song’ }]
> 
> Hopefully that’ll help someone out sometime. 🙂

Yeah! You help me with this post

---

<div class="post-metadata">

**Author:** ![adam](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4af34b/32.png) [@adam](https://www.ruby-forum.com/u/adam)\
**Post date:** [December 3, 2010, 3:04pm UTC](https://www.ruby-forum.com/t/how-to-set-up-a-has-many-through-association-with-ultrasphinx/160933/4 "2010-12-03T15:04:11Z")

</div>

Thanx for your information. Wasting half a day I found your post and  
this help me so much.

Thanx a lot 🙂