Wrong matching results with Sphinx

Question

Mysql query in sphinx config:

SELECT
    c.id,
    c.name,
    p.vendor
FROM
    categories AS c
LEFT JOIN (
    SELECT DISTINCT
        vendor,
        category_id
    FROM
        clothes
) p ON c.id = p.category_id
ORDER BY
    c.name

Query gives such structure (let me call this result A):

id  name    vendor
178 dress   Liu Jo Jeans
178 dress   Bergamoda
178 dress   Rifle
190 dress   Moda Corazon
190 dress   Bergamoda
205 boots   Bergamoda

Sphinx match mode is SPH_MATCH_ANY. Troubles begin when I'm trying to make Query('dress'), this results

178 dress   Liu Jo Jeans
190 dress   Moda Corazon

But I need all results where name or vendor field contains 'dress' (expected result A without boots).

What am I doing wrong? Thanks in advance!

UPDATE for Danilo Bustos
categories structure: id (integer,auto increment), name (varchar,255), parent_id (integer).
clothes structure: id (integer,auto increment), name (varchar,255), vendor (varchar,255)

Expecting result:

id  name    vendor
178 dress   Liu Jo Jeans
178 dress   Bergamoda
178 dress   Rifle
190 dress   Moda Corazon
190 dress   Bergamoda

Can you copy the structure of the tables and define an example of the expected result? — Danilo Bustos, Feb 14 '17 at 20:59

score 1 · Accepted Answer · answered Feb 15 '17 at 10:39

One of your main problems is 'document-id' in sphinx should be unique. You seem to be use category_id. Yet, there are multiple rows in your result A with the same id. THis leads to unpredictable results in Sphinx.

In general seems like you should use the id from clothes as the document-id. So can get multiple rows, as it should be unique. You can put category_id in a sphinx attribute, if still want the resultset to contain it.

(perhaps will have to change your 'inner' query to get the id in the join table, use GROUP BY rather than DISTINCT to deduplicate. although perhaps dont even need it, just a standard JOIN between two tables may do)

Wrong matching results with Sphinx

1 Answers1