Index a boolean field as a string in sphinx

Question

I have a Ruby on Rails site that uses thinking sphinx for searching a postgres database.

One of the fields in a table I am searching on is a boolean. I'd like to be able to match on that boolean when a certain keyword is used in the search query.

To fully explain with an example:
My site is for people who develop their own black and white film.
I have a recipe table where people describe how they develop a film. That table has a boolean column called "stand_developed" (a method of developing film).
I'd like to return results where that field is true when the user searches for the word "stand".

I've been through the sphinx docs and not really found if it's possible.

I guess I could hack something inside my controller method by parsing the query terms and adding a condition but is there a cleaner way of doing it?

Answer 1

This is what I've done as far as searching on boolean fields using ThinkingSphinx. Pass stand_developed as a URL parameter along with your query_string in the following ways:

1. URL for a general query without search on stand_developed will be http://yoursite.com/search?q=your_query_string
2. URL for query with stand_developed == TRUE will be http://yoursite.com/search?q=your_query_string&stand_developed=1
3. URL for query with stand_developed == FALSE will be http://yoursite.com/search?q=your_query_string&stand_developed=0

Then, in your controller, you would do this:

if params[:stand_developed]  && params[:stand_developed].to_i == 1
  # perform query search with stand_developed == true
  @search_results = YourModel.search(params[:q], :with => {:stand_developed => true})
elsif params[:stand_developed]  && params[:stand_developed].to_i == 0
  # perform query search with stand_developed == false
  @search_results = YourModel.search(params[:q], :with => {:stand_developed => false})
else
  # perform general query search
  @search_results = YourModel.search(params[:q])
end

Answer 2

I've come up with a solution to this now.

I tried the "hack" I mentioned in my question - parsing the word "stand" and searching for it explicitly (a variation on the answer from Paul Davis) but it didn't work very well.

I didn't explain this in my question (didn't realise the full implications at the time I asked) but I need it to also match if the user used the word "stand" in their recipe description too.

So, I tried to get sphinx to add a condition along the lines of "if stand_developed is true or notes contains 'stand'" but I couldn't seem to find the right syntax for that. It also had to deal with any other search text too.

In the end I added an extra column to my recipe table called "search_tags" and I add the word "stand" into it if the user selects "stand_developed" when adding a recipe. I then get Sphinx to index that field as well as my other fields and it all works perfectly.

Answer 3

You could have just tested if params[:search] included the text 'stand' then done the searching from there. You don't need an extra column in your table, that's just overhead that's not needed.

if params[:search].downcase.include?('stand')
  Model.search params[:search], :with => {:stand_developed => true}
else
  Model.search params[:search]
end