Forum: Ruby on Rails How to set up a has_many :through association with Ultrasphinx

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
976f360b2bd389be93240592a1c99901?d=identicon&s=25 Adam (Guest)
on 2009-02-21 02:19
(Received via mailing list)
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
976f360b2bd389be93240592a1c99901?d=identicon&s=25 Adam (Guest)
on 2009-02-21 03:44
(Received via mailing list)
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. :)
313b624e1083873360551ff966e04263?d=identicon&s=25 Paco Guzman (pacoguzman)
on 2009-11-02 13:23
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
35051b0a5ae78866e69f61a8d73e1d6e?d=identicon&s=25 Manish Nautiyal (manish812)
on 2010-12-03 15:04
Thanx for your information. Wasting half a day I found your post and
this help me so much.

Thanx a lot  :)
This topic is locked and can not be replied to.