CODEWITHSUNDEEP
ruby-on-rails-3thinking-sphinx
alex
alex

Reputation: 1900

Searching has_many relationship with Rails and Thinking Sphinx

I am trying to build a Search fonction for my Rails 3 app with Sphinx 0.9.10 and Thinking Sphinx. I want users to search songs. My songs can have many tracks. For instance the song "Beautiful song", could have an audio track linking to an mp3, and a video track linking to youtube.

For now, my search function works well when I have only 1 track associated to a Song, but is buggy when I have several tracks...

My users should be able to enter a keyword, and to filter to find songs that have at least one audio track, at least one video track, or at least one of each.

So far, I have the following model for my Songs:

class Song < ActiveRecord::Base
  ...
  has_many :tracks
  ...

  define_index('song') do
    indexes                               :name, 
                                          :sortable   => :true
    has     "tracks.media LIKE '%audio%'",:as         => :audio,
                                          :type       => :boolean
    has     "tracks.media LIKE '%video%'",:as         => :video,
                                          :type       => :boolean
  end
end

In my Track model, I am using a serialized media field. Ruby sees it as a hash, containing among others a link and a type (for instance 'audio/m4u' or 'video/mp4'). MySQL sees it as a string.

class Track < ActiveRecord::Base
  belongs_to :Song

  serialize :media
end

In my Song controller, I have something like this:

class CoursesController < ApplicationController
  def search
      with_params[:audio] = true if search[:audio] == true || search[:audio] == 'true'  || search[:audio] == "1"
      with_params[:video] = true if search[:video] == true || search[:video] == 'true' || search[:video] == "1"

      @songs = Song.search(
      keywords,
      :with       => with_params
      ...
      )
      ...
  end
end

The search results are fine when I have only one Track associated to a Song.

But when I have several Tracks, it seems the search index only sees the first track: if "Beautiful song" has a first track "video" and a second one "audio", it will only show in the search results if I select "video" in my filters, no if I select "audio".

What am I missing?

Upvotes: 1

Views: 632

Answers (1)

pat
pat

Reputation: 16226

What you'll need to do is compare against the concatenated values of the media column. In MySQL, that'll look something like this:

GROUP_CONCAT(`tracks`.`media` SEPARATOR ' ')

Or for PostgreSQL (using 8.4 or better - let me know if you're using an older version):

array_to_string(array_agg("tracks"."media"), ' ')

Upvotes: 2

Related Questions