CODEWITHSUNDEEP
sphinx
William King
William King

Reputation: 1268

Complex SphinxQL Query

I'm trying to write a SphinxQL query that would replicate the following MySQL in a Sphinx RT index:

SELECT id FROM table WHERE colA LIKE 'valA' AND (colB = valB OR colC = valC OR ... colX = valX ... OR colY LIKE 'valY' .. OR colZ LIKE 'valZ')

As you can see I'm trying to get all the rows where one string column matches a certain value, AND matches any one of a list of values, which mixes and matches string and integer columns / values)

This is what I've gotten so far in SphinxQL:

SELECT id, (intColA = intValA OR intColB = intValB ...) as intCheck FROM rt_index WHERE MATCH('@requiredMatch = requiredValue');

The problem I'm running into is in matching all of the potential optional string values. The best possible query (if multiple MATCH statements were allowed and they were allowed as expressions) would be something like

SELECT id, (intColA = intValA OR MATCH('@checkColA valA|valB') OR ...) as optionalMatches FROM rt_index WHERE optionalMatches = 1 AND MATCH('@requireCol requiredVal')

I can see a potential way to do this with CRC32 string conversions and MVA attributes but these aren't supported with RT Indexes and I REALLY would prefer not switch from them.

Upvotes: 0

Views: 1438

Answers (1)

barryhunter
barryhunter

Reputation: 21091

One way would be to simply convert all your columns to normal fields. Then you can put all this logic inside the MATCH(..). Ie not using attributes.

Yes you can only have one MATCH per query.

Otherwise, yes you could use the CRC trick to make string attributes into integer ones, so can use for filtering.

Not sure why you would need MVA, but they are now supported in RT indexes in 2.0.2

Upvotes: 2

Related Questions