0

I'm working on a project where I use Sphinx searchengine. But - as I realized - the Sphinx documentation is big but hard to understand.

So I was not able to find any information on how to use the WHERE clause to filter behind a MATCH-statement. What I tried yet is:

"SELECT *, country FROM all_gebrauchte_products WHERE MATCH('@searchtext (".$searchQuery.")') AND country='".$where."' ORDER BY WEIGHT() DESC LIMIT ".$page.", ".$limit." OPTION ranker=expr('sum(lcs)')"

If I use it without the country=$where clause, I get back many GUIDs but from different countries. So somehow I have to filter the country column;

If I use the above statement, I get error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 index all_gebrauchte_products: parse error: unknown column: country'

But I set the index like this:

sql_query_range = SELECT MIN(gebr_id), MAX(gebr_id) FROM all_gebrauchte_products
sql_range_step = 10000
sql_query = \
            SELECT a.gebr_id AS guid, 'products' AS data_type, a.gebr_products AS products, a.gebr_user AS username, a.gebr_date AS datadate, CONCAT(a.gebr_hersteller,' ', a.gebr_modell,' ', a.gebr_ukat,' ', a.gebr_kat,' ', a.gebr_bemerkung) AS searchtext, a.gebr_bild1 AS image1, a.gebr_bild2 AS image2, a.gebr_bild3 AS image3, a.gebr_bild4 AS image4, a.gebr_bild5 AS image5, b.h_land AS country, b.h_web AS weblink, b.h_firmenname AS company, b.h_strasse AS street, b.h_plz AS zipcode, b.h_ort AS city, a.gebr_aktiv AS active \
            FROM all_gebrauchte_products a, all_haendler b \
            WHERE a.gebr_user = b.h_loginname AND a.gebr_id>=$start AND a.gebr_id<=$end
sql_attr_uint = active

Can anybody tell me what is going wrong? Or how do I have to filter for country?

Thnx. in advance for your help.

Ingmar Erdös
  • 517
  • 8
  • 18

1 Answers1

1

Any columns in the sql_query you dont make an ATTRIBUTE, is automatically a FIELD (except the first column is always the document-id).

FIELDs are 'full-text' indexed, they are what you can match in the query - ie the MATCH(...) clause.

ATTRIBUTES are what can be 'filtered' in WHERE, sorted by in ORDER BY, grouped in GROUP BY, or retrieved in the SELECT (or even used in ranking expressions).

So you need country to be an ATTRIBUTE to be able use it in WHERE filter

You don't say but guess it's a string. You can use sql_field_string to make a column BOTH a FIELD and ATTRIBUTE, if you are still interested in being able to full-text query the column too.

(also because its a string, need a very recent version of sphinx. Sphinx only recently gained ability to filter by strings attributes)

barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • Thank you for your fast response... but I can't get any usable information out of your answer. I don't know what to do with sql_field_string. In my question you can see, how I prepared the index in the sphinx.conf, how I made the sphinxql query and what I get back from it. As I'm a newcomer to Sphinx, please try to give me an answer (eg. with examples) that a newcomer also can understand. Thank you so much in advance. – Ingmar Erdös Nov 27 '15 at 10:07
  • You've used `sql_attr_uint` http://sphinxsearch.com/docs/current.html#conf-sql-attr-uint to make a (unsigned) interger Attribute already. `sql_field_string ` is used in the config file, in almost exactly the same way http://sphinxsearch.com/docs/current.html#conf-sql-field-string – barryhunter Nov 27 '15 at 13:14