Реализация фильтра по свойствам. Как ограничить поиск?

Question

[Pupochkin123]

Нужен сложный фильтр по свойствам с привязкой к категориям.

Схема базы

Как на основе выбранных фильтров произвести ограниченный поиск?

SELECT * FROM products P, product_categories PC, product_options PO
  WHERE P.id = PC.product_id AND P.id = PO.product_id
    AND PC.category_id = 3
    AND PO.classifier_id in (1,2)
    AND PO.option_id in (5, 9999)

результаты не сокращаются(в случае если нет 100% совпадения по свойствам)

свойства 9999 не существует, если свойства нет, запись в результатах должна отсутствовать.

Comments

[alexalexes]

Хорошо бы еще знать назначения связей между таблицами. хотя бы по примеру наличия внешних ключей category_id в разных таблицах - какова их роль? Нужно ли по разному применять их при выборе критериев поиска?
Еще:

AND
PC.category_id = 3
AND
PO.classifier_id in (1,2)
AND
PO.option_id in (5, 9999)

Вот эту часть лучше словами описать. Очень легко допустить расхождения того, что предполагается найти и как это реализуется в запросе (можно легко перепутать or и and или не правильно трактовать эти И/ИЛИ в человеческом описании критериев поиска).

[Pupochkin123]

alexalexes, Здравствуйте, спасибо за замечание:
PC - product_categories (в каких категориях может быть товар),
PO - product_options (какие опции могут быть у товара, т.е свойства)

[Ипатьев]

alexalexes, ну это классический eav, с его адовым поиском

[Ипатьев]

я бы порекомендовал сделать для опций json-поля вместо вот этого вот всего

[Pupochkin123]

Ипатьев, JSON как решение не подойдет из-за специфики постановки задачи, не подскажите как решить именно в рамках концепции EAV? Спасибо)

[Ипатьев]

JSON как решение ПРЕКРАСНО подойдёт для любой специфики постановки задачи, если жить не в 20 веке, а в 21.
Хочется мазохизма - в интернете полно библиотек для работы с EAV

[Pupochkin123]

Ипатьев, Можете пожалуйста приложить в ответах предложенное Вами решение на основе JSONB, как должна выглядеть структура и фильтрация. Спасибо!

[Ипатьев]

Стандартно, через JSON_EXTRACT()

Answer 1

[Дмитрий]

SELECT * FROM products P, product_categories PC
WHERE P.id = PC.product_id
AND
PC.category_id = 3
AND 
(select count(*) from product_options as PO where P.id = PO.product_id AND PO.classifier_id in (1,2) AND PO.option_id in (5, 9999)) = 2