sphinxsphinxql

How to use SNIPPET function in SphinxQL?

I have an index:

--------------------------------------------------
| id |  name  | folder | tag1  | tag2  |  topic  |
--------------------------------------------------
| 1  | file 1 |  AAA   | [1,2] | [3,4] |    1    |
--------------------------------------------------  
| 2  | file 2 |  BBB   |  [1]  | [4,5] |    1    |
--------------------------------------------------  
| 3  | file 3 |  AAA   |  [2]  |  [4]  |    2    |
--------------------------------------------------

What I need to do is to filter files by tag and the following request works fine:

SELECT id, name,
  ANY(var=1 FOR var IN tag1) as tag_filter_1, 
  ANY(var=5 FOR var IN tag2) as tag_filter_2,
  GROUP_CONCAT(id) as files 
FROM index 
WHERE tag_filter_1 = 1 AND tag_filter_2 = 1
GROUP BY topic;

Now I need to modify the query to apply the tag1 filter only for the files from AAA folder and at the same time keep filtering by tag2 from all the folders.

I was thinking about OR condition but it's not supported. Also, the option was to use GROUP_CONCAT(tag1) and then filter the Sphinx results in PHP but tag1 is JSON, not scalar.

I am wondering if it's possible to solve this using SNIPPET or IF function and how. Or any other ideas?

Solution

  • It's maybe a little difficult to see, but can just do... 

    SELECT id, name, 
  ANY(var=1 FOR var IN tag1) OR NOT folder='AAA' AS tag_filter_1, 
  ANY(var=5 FOR var IN tag2) AS tag_filter_2
FROM index WHERE tag_filter_1 = 1 AND tag_filter_2 = 1;

    tag_filter_1 becomes tag1 filter applies, OR not AAA folder.

    So a document in AAA folder, needs to match the tag1 filter (because can't match the second part)

    .. but document in other folders will always match because of OR, so the tag1 filter is ignored.

    (there isnt a != string operator, so need to invert the use of = operator :)