Search code examples
sphinxsphinxql

How to return rows that do not match a value in a column?

The Sphinx documentation has a simple example on using SphinxQL:

SELECT * FROM test WHERE MATCH('@title hello @body world')

Let's say I add a varchar column to the table called ids which contains integer values separated by something such as a comma. For example:

-------------------------------
| title | body  | ids         |
-------------------------------
| hello | world | 5,251,87580 |
-------------------------------

If I wanted to amend the above example query to select rows only that contain a specific ID, then I think I could simply change it to this:

SELECT * FROM test WHERE MATCH('@title hello @body world @ids 251')

Right?

However, what I'd like to do is actually return rows that do not contain a certain ID in the ids column. Is this possible?

EDIT:

I thought I found the answer, as by branched out from the SphinxQL documentation I found under "boolean expressions" or something similar that you can simply put the exact phrase (or in this case, number) in quotes and then put a minus sign in front.

So the example query simply becomes:

SELECT * FROM test WHERE MATCH('@title hello @body world @ids -"251"')

I thought it worked, but it doesn't.

If I change the ID I don't want to match from 251 to 11, and the following column exists in the table:

-------------------------------
| title | body  | ids         |
-------------------------------
| hello | world | 5,111,87580 |
-------------------------------

Then this query:

SELECT * FROM test WHERE MATCH('@title hello @body world @ids -"11"')

Doesn't return that row because Sphinx is apparently matching "11" inside of "111".

There are my conf settings:

index_field_lengths = 1
morphology          = stem_en
min_word_len        = 1
min_prefix_len      = 1
prefix_fields       = name
expand_keywords     = 1
Solution

  • I branched out from the SphinxQL documentation and found under "boolean expressions" or something similar that you can simply put the exact phrase (or in this case, number) in quotes and then put a minus sign in front.

    So the example query simply becomes:

    SELECT * FROM test WHERE MATCH('@title hello @body world @ids -"251"')

    I tested it, and it seems to work!

    I guess I'll leave the question here in the event someone else might find it useful :-)