How to use SNIPPET function in SphinxQL?

Question

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?

no idea why think SNIPPET might be related? Thats for creating a highlighted snippet. Taking a large block of text and extracting bits that match a text query. Nothing at all to do with filtering. — barryhunter, Jul 29 '19 at 16:21

score 0 · Accepted Answer · answered Jul 29 '19 at 16:19

0

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

answered Jul 29 '19 at 16:19

barryhunter

20,886
3
30
43

Note, on the use of `OR` - it works in the SELECT part. Its only on the WHERE side its not supported! – barryhunter Jul 29 '19 at 16:22
Once again you saved me tonnes of time. Thank you. Works perfectly. Anyway, the request looks so obvious, don't know why I got focused on SNIPPET function ;) – slavkovl Jul 30 '19 at 08:07

How to use SNIPPET function in SphinxQL?

1 Answers1