Sphinx search in product params table (product attributes)

Question

Good day!

I have some troubles in my sphinx search. I want to search in product params, but can't understand how can I do this.

For example.

Products table

product_id INT
product_name STRING

Product Params table

param_id INT
product_id INT
param_name STRING
param_value STRING

And this data for example:

Products

1 TV
2 Player

Params

1 1 Size Big
2 1 Color Red
3 1 Weight 3.0
4 2 Size Small
5 2 Color Brown
6 2 Length 20

And I want to search all products where Size Is Small OR Color Is Red

Answer 1

There are many different ways. Perhaps one of the simplest:

sql_query = SELECT product_id, product_name, \
   GROUP_CONCAT(CONCAT(param_name,'_',param_value)) AS params \
   FROM products LEFT JOIN params USING (product_id) \
   GROUP BY product_id \
   ORDER BY NULL

(Thats actully mysql query, but sql-server cant be that much different)

Can then just do (SphinxQL) queries like

SELECT * FROM index WHERE MATCH('keyword @params (Size_Small | Color_Red)');

(if any of your params have spaces, might need to use REPLACE to change to understores, to keep them as one word - or use "phrase syntax")