CODEWITHSUNDEEP
mysqlsphinx
966p
966p

Reputation: 585

Wrong matching results with Sphinx

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

Upvotes: 0

Views: 68

Answers (1)

barryhunter
barryhunter

Reputation: 21081

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)

Upvotes: 1

Related Questions