Searching for empty string in SphinxQL

Question

I have a string field 'extra_data' in Sphinx index, which has data in comma-separated format (ex. 'green', 'red', 'green,red', ...). And I need to select to find rows: 1. extra_data is 'green' 2. or extra_data is 'red' 3. or extra_data is empty

So I can write query:

SELECT id FROM sphinx WHERE MATCH('@extra_data (^green$|^red$))')

But how should I write query to find empty extra_data ('') too?

Answer 1

Sphinx doesnt 'index' nothing. It indexes words :)

So you have to arrange for empty to be a word that can be indexed.

eg for a database backed index..

sql_query = SELECT id,title, ... , IF(extra_data='','_empty',extra_data) AS extra_data FROM mytable ... 

Then can do

... WHERE MATCH('@extra_data (^green$|^red$|_empty)')