Replacement for 'OR' in SphinxQL

Question

I'm currently trying to integrate Sphinx search engine into Python application. The problem is that SphinxQL doesn't support OR clause as common SQL does. There are some hacks to use, like writing expressions in SELECT like this:

SELECT id,(field1 = val1 OR field2 = val2) as expr FROM foo_bar WHERE expr = 1;

However, it doesn't work with strings, because they should be handled using MATCH function. So I decided to divide query into separate subqueries and combine results obtained. Yet there's still a problem of getting a proper META information, especially the total_found field. Sphinx counts it for separate queries, but rows obtained from these queries may intersect and I have no ability to check it (database is large).

I believe there must be a solution. I'm using Sphinxit (SphinxAlchemy has a version conflict with SQLAlchemy I'm using).

Repost from SphinxSearch forum:

I have a table I need to search in with text and numerical columns as well. I need to write a query with OR condition; found out that there's a way to do it using SELECT expressions like:

SELECT *, quantity>=50 OR quantity=0 AS mycond FROM table1 WHERE mycond = 1;

Hopelessly it doesn't work with string attributes. This query isn't parsed:

SELECT *, category='foo' OR category='bar' AS mycond FROM table1 WHERE mycond = 1;

Yet this is working in Beta 2.2.3:

SELECT * FROM table1 WHERE category='foo';

What should I do to find count of rows that fit one of conditions, not every one of them? I can make a few queries and merge obtained items into one list, but I need to now how much of these rows are in the database now.

Answer 1

For attribute / facet OR'ing, I think you're correct that the only way is to put an expression in the SELECT clause.

For strings, though, check out the documentation on the fulltext query syntax. You can't exactly use the OR keyword, but something like this should work:

SELECT id, name 
FROM recipes
WHERE MATCH('(@ingredients chocolate) | (@name cake)')
LIMIT 10;