Search code examples
sphinx

How to use NOT in Sphinx?

Documentation says:

WHERE clause. This clause will map both to fulltext query and filters. Comparison operators (=, !=, <, >, <=, >=), IN, AND, NOT, and BETWEEN are all supported and map directly to filters

But when I try to select data using next query I have trouble:

mysql> select * from content where NOT sentiment = 1;
ERROR 1064 (42000): sphinxql: syntax error, unexpected NOT, expecting IDENT (or 56 other tokens) near 'NOT sentiment = 1'

What is wrong?

Solution

  • NOT is an operator that is generally used in conjunction with another operator such as IN or when dealing with a boolean or NULL;

    Valid examples:

    SELECT * FROM content WHERE sentiment IS NOT TRUE;
SELECT * FROM content WHERE sentiment IS NOT NULL;
SELECT * FROM content WHERE sentiment NOT IN (1, 2);
SELECT * FROM content WHERE sentiment NOT BETWEEN 1 AND 5;

    You can also use it as a logical NOT where it'll invert zero and non-zero values:

    SELECT NOT (1+3); /* 0 */
SELECT NOT 0;     /* 1 */

    You query should use the != or <> operator:

    SELECT * FROM content WHERE sentiment != 1;
SELECT * FROM content WHERE sentiment <> 1;