CODEWITHSUNDEEP
sphinx
Abhilash Joseph
Abhilash Joseph

Reputation: 1196

Using 'NOT' operator in Sphinx Search?

I have a index 'order' with field id, date, and status. I need to get all order not having status CLOSED.

My sphinx Query is :

SELECT id, status FROM order 
WHERE MATCH('@status "!CLOSED"') AND id < 21;


+------+-------------+
| id   | status      | 
+------+-------------+
|   10 | CLOSED      |
|    1 | CLOSED      |
|    4 | CLOSED      |
|    5 | CLOSED      |
|    7 | CLOSED      |
|    9 | CLOSED      |
|   14 | CLOSED      |
|   18 | CLOSED      |
|   19 | CLOSED      |
+------+-------------+
9 rows in set (0.06 sec)

Where I went wrong? If I want to do NOT in multiple field how to do that? Can you please rewrite this SphinxQL for me?

Upvotes: 1

Views: 1886

Answers (1)

barryhunter
barryhunter

Reputation: 21091

You've put the term in phrase operators, so its looking for the phrase !CLOSED - but as I doubt that you have ! in your charset_table its ignored, so becomes just a search for CLOSED. 

... WHERE MATCH('@status !CLOSED')

would in theory work. But sphinx can't execute a query with just 'NOT' terms. Sphinx has an inverted index, listing which words are in which documents. It doesnt have a list of all documents that it can then 'remove' specific ones.

... so you need a list of all documents. Easiest is to add a fake keyword to all documents, eg modify your source definition (in sphinx.conf) to add a fake field something like... 

sql_query = SELECT id,...,'_all' as fake FROM sqltable ...

Then can using it in your sphinx queries.... 

... WHERE MATCH('_all @status -CLOSED')

Upvotes: 4

Related Questions