0

How do I make a filtering engine for my online shop?

My situation: I have tables

/* Stored products variants */
products_variants (id_product_variant,title,...)
/* Stored parameters with its values */
parameters(id_parameter, units, title,...)
/* Stored connection between variant and parameter value */
products_variants_parameters (parameter_id, product_id)

I need to do this: The user selects value1,value2 of parameter1 and value2 of parameter2 for example, so I need to select variants which has: value1 or value2 of parameter1 AND value2 of parameter2.

I tried to do something like this:

 products_variants_parameters.parameter_id IN (value1, value2) AND
 products_variants_parameters.parameter_id IN (value2)

But it returns an empty result. How do I update it to make it working?

EXAMPLE DATA:

products_variants [id_product_variant, title]
{
[1,Product 1],
[2,Product 2],
[3,Product 3]
}

parameters [id_parameter, title]
{
[1,Color: red],
[2,Color: blue],
[3,Size: XXL],
[4,Size: M]
}

products_parameters [product_id, parameter_id]
{
[1,1],
[1,4],
[2,1],
[2,3],
[3,2],
[3,4]
}


Customer checked:
Color: red
Color: blue
Size: M

==> So I want to return all products with color Red or Blue and in size M. For this example data products:
1 - Product 1 (Red, M)
3 - Product 3 (Blue, M)

SOLVED: Thanks this thread: How to filter SQL results in a has-many-through relation

Community
  • 1
  • 1
Jan Kožušník
  • 683
  • 3
  • 16
  • 30
  • Your logic is all wrong. You're looking at `parameter_id` twice. This will only return data where it's equal to Value2 the second one overrides the first one. You could do an OR statement instead of an AND. But there's still a logic issue. – ZeroBased_IX Mar 01 '15 at 13:06
  • So how should I do it? Could you describe me schema of query, to gain good result? – Jan Kožušník Mar 01 '15 at 13:23

1 Answers1

0

I think, you want to be found all products (product_id) with certain parameters. So you filter with the title or id of parameter and found all product_ids:

SELECT product_id FROM `products_variants_parameters` WHERE parameter_id IN (SELECT id_parameter FROM parameters WHERE title IN ('Value1','Value2')) GROUP BY product_id
cSteusloff
  • 2,487
  • 7
  • 30
  • 51
  • Yeah, almost. But it's more difficult: I have for example 2 parameters with values. So I need to select products with values from 1st parameter and values from 2nd parameter – Jan Kožušník Mar 01 '15 at 15:11
  • It is possible to construct an example with sample data and your desired result? – cSteusloff Mar 01 '15 at 15:17
  • Ok, I will give some example into my question – Jan Kožušník Mar 01 '15 at 19:12
  • I think you miss a parameter group in your database design. At moment you store two information in one column. Is the colon delimiter (sign :) fix, than it's possible. – cSteusloff Mar 01 '15 at 21:30
  • I managed to make it working. Really helpful was this thread: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation – Jan Kožušník Mar 01 '15 at 22:46