0

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.

Ilya Khaustov
  • 328
  • 1
  • 6
  • 21
  • You may want to consider editing the title. I originally came into this question expecting to find a technology recommendation request. – jpmc26 May 27 '14 at 09:24
  • I thought about it. Couldn't pick a better title. Suggestions are welcome. – Ilya Khaustov May 27 '14 at 09:57
  • 1
    "Replacement for 'OR' in SphinxQL query" came to mind. – jpmc26 May 27 '14 at 14:26
  • It may help to include an actual query you want to run, even it not actully a valid sphinxql query. At the moment its not really possible to suggest alternative because dont know what part having problem with. For example it may be possible edit your MATCH query. – barryhunter May 27 '14 at 16:50
  • 1
    oh, and re the comment of filtering strings, see http://sphinxsearch.com/blog/2014/05/15/sphinx-2-2-3-beta-is-now-available/ – barryhunter May 27 '14 at 16:59
  • Strings filtering is great, but it doesn't help with OR. OR is supported only in expressions, where strings filtering doesn't work: `SELECT id, (name = 'S5cb4110') AS _expr FROM tbl_users WHERE _expr=1 LIMIT 0, 10;` fails to be parsed. – Ilya Khaustov May 28 '14 at 06:14

1 Answers1

2

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;
Don McCurdy
  • 10,975
  • 2
  • 37
  • 75