How to make such a complex query with multiple parameters?

Question

MySQL

I've been suffering for a week now, I can't solve this problem. Please help.
There is a table with product parameters
product_product_options (ppo)
id, product_id, option_id, option_title, option_value

Sample values ​​in the tables:
3, 5, 5, 'Color', 'Red'
4, 5, 5, 'Color', 'Black'
5 , 5, 5, 'Size', '35'

And a table where it is indicated which option belongs to the product and which category the product has.
Depending on the category ID, options are displayed in certain categories.

Approximate values ​​in the tables
id, ppo_id, product_id, category_id
1, 3, 5, 100

These options are displayed on the site as checkboxes.

Colors:


35
36

You need to make a selection so that if the user selects red, then only products in red are displayed, and if he selects two checkboxes: red and size 35, then red clothes with size 35, respectively.

What selection can be made here? I didn't even have options. Thanks in advance.

Answer 1

I believe that you have a typo in your conditions and option_id still uniquely describes a possible option.
If this is not the case, my queries will need to be rewritten under a unique condition (well, or put things in order in the data structure, which is more painful, but generally more correct).
The simplest solution:

select product.* from product where
product_id in ( select product_id from product_options where option_id = 5 and option_value = "Красный" )
and 
product_id in ( select product_id from product_options where option_id = 6 and option_value = "35" )

In some cases, it may be more profitable in terms of performance to make subqueries to get the id of each category separately, and combine them in a programming language.
At the same time, this will allow you to do a fuzzy search, for example, when the matches are not complete.
If you want to pervert, you can experiment with a request like

select product.product_id, count(product_options.id) cnt from product 
join product_options on product_options.product_id = product.product_id AND ((option_id = 5 and option_value = "Красный" ) or (option_id = 6 and option_value = "35" ))
group by product.product_id
having cnt = 2

On a number of requests it can be faster
PS In general, there is an opinion that solving this in Sql in a real project is not very correct in terms of load, it is better to fasten some kind of elastic search.