How to set up a has_many :through association with Ultrasphinx

rubyonrails-talk
1

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

2

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. :slight_smile:

3

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

Thanx a lot :slight_smile: