Rails - Feb 2009 - How to set up a has_many :through association with Ultrasphinx

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


On Feb 20, 5:19 pm, Adam
<adam.p...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
Posted via http://www.ruby-forum.com/.

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

Thanx a lot  :)

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.