复杂的 SphinxQL 查询
我正在尝试编写一个 SphinxQL 查询，它将在 Sphinx RT 索引中复制以下 MySQL：
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')
如您所见，我正在尝试获取一个字符串列与某个值匹配的所有行，并且与列表中的任何一个匹配的值，混合并匹配字符串和整数列/值）
这是我到目前为止在 SphinxQL 中得到的：
SELECT id, (intColA = intValA OR intColB = intValB ...) as intCheck FROM rt_index WHERE MATCH('@requiredMatch = requiredValue');
我遇到的问题是匹配所有潜在的可选字符串值。最好的可能查询（如果允许多个 MATCH 语句并且允许它们作为表达式）将类似于
SELECT id, (intColA = intValA OR MATCH('@checkColA valA|valB') OR ...) as optionalMatches FROM rt_index WHERE optionalMatches = 1 AND MATCH('@requireCol requiredVal')
我可以看到一种使用 CRC32 字符串转换和 MVA 属性来执行此操作的潜在方法，但 RT 索引不支持这些，我真的宁愿不离开他们。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
一种方法是将所有列简单地转换为普通字段。然后您可以将所有这些逻辑放入 MATCH(..) 中。即不使用属性。
是的，每个查询只能有一个 MATCH。
否则，是的，您可以使用 CRC 技巧将字符串属性转换为整数，以便可以用于过滤。
不确定为什么需要 MVA，但现在 2.0.2 中的 RT 索引支持它们
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