0

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
Nate
  • 26,164
  • 34
  • 130
  • 214
  • 1
    What version of sphinx is this? I guess you have an old version that has the default of `enable_star=0`. Read the documentation from `enable_star`, you probably want to change that. Its also possible you have `dict=keywords`? - as I think that totally ignores `prefix_fields` – barryhunter Mar 25 '15 at 11:41
  • 1
    Doh, just saw you have `expand_keywords=1` that makes an index *act* like `enable_star=0`. ... So read the documentation for `expand_keywords`, as it make substring matches happen. The comment about dict=keywords and prefix_fields still stands. – barryhunter Mar 25 '15 at 11:43
  • @barryhunter I'm using Sphinx 2.2.7. I do not have `dict=keywords`. I posted all of the index settings. So there's no way to force an exact match when `expand_keywords=1`? – Nate Mar 25 '15 at 12:22
  • 1
    Ah that version I think has dict=keywords as the default, which explains why prefix_fields has no effect. You could use `dict=crc` to enable `prefix_fields` which would fix this specific case. But in general there is no override for expand_keywords, could possibly try `=` exact form modifier, but not sure it will work. – barryhunter Mar 25 '15 at 13:39

1 Answers1

0

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 :-)

Nate
  • 26,164
  • 34
  • 130
  • 214