Thinking Sphinx sql like query in a condition for a field

Question

I had these queries, but now I'm trying to use sphinx, and I need to replace them, but I can't find a way to do this:

p1 = Product.where "category LIKE ?", "#{WORD}"
p2 = Product.where "category LIKE ?", "#{WORD}.%"
product_list = p1 + p2

I'm doing the search over a model named "Product" in "category" field; I need a way to replace "#" and "%" in sphinx. I have a basic idea of how to do that, but this isn't working:

Product.search conditions: {category: "('WORD' | 'WORD.*')"}

Answer 1

There's a few things to note.

• If you want to match on prefixes, make sure you have min_prefix_len set to 1 or greater (the smaller, the more accurate, but also the slower your searches will be, and the larger your index files will get). Also, you need enable_star set to true. Both of these settings belong in config/thinking_sphinx.yml (there's examples in the docs).
• Single quotes have no purpose in Sphinx searches, and will be ignored - but I don't think that's a problem with what you're trying to search with.
• Full stops, however, are treated as word separators by default. You can change this with charset_table - but that means all full stops in all fields will be treated as part of words (say, at the end of sentences), so I wouldn't recommend it.

However, if full stops are ignored, then each word in the category field is indexed separately, and so without any extra settings, this should work:

Product.search conditions: {category: WORD}