Is it possible to use Sphinx search with dynamic conditions?

Question

In my web app I need to perform 3 types of searching on items table with the following conditions:

1. items.is_public = 1 (use title field for indexing) - a lot of results can be retrieved(cardinality is much higher than in other cases)

2. items.category_id = {X} (use title + private_notes fields for indexing) - usually less than 100 results

3. items.user_id = {X} (use title + private_notes fields for indexing) - usually less than 100 results

I can't find a way to make Sphinx work in all these cases, but it works well in 1st case. Should I use Sphinx just for the 1st case and use plain old "slow" FULLTEXT searching in MySQL(at least because of lower cardinality in 2-3 cases)?

Or is it just me and Sphinx can do pretty much everything?

Answer 1

Without full knowledge of your models I might be missing something, but how's this:

class item < ActiveRecord::Base
  define_index do
    indexes :title
    indexes :private_notes
    has :is_public, :type => :boolean
    has :category_id
    has :user_id
  end
end

1)

Item.search(:conditions => {:title => "blah"}, :with => {:is_public => true})

2)

Item.search("blah", :with => {:category_id => 1})

3)

Item.search("blah", :with => {:user_id => 196})