Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE product (
  id INT NOT NULL PRIMARY KEY,
  template_id INT NOT NULL,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE template (
  id INT NOT NULL PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE template_property (
  id INT NOT NULL PRIMARY KEY,
  template_id INT NOT NULL,
  title TEXT NOT NULL
);

CREATE TABLE


CREATE TABLE product_property_value (
  id INT NOT NULL PRIMARY KEY,
  product_id INT NOT NULL,
  property_id INT NOT NULL,
  value TEXT
);

CREATE TABLE


INSERT INTO template(id, title)
VALUES 
  (1, 'Телевизор'), (2, 'Смартфон'), (3, 'Наушники');

INSERT INTO product(id, template_id, title)
VALUES 
  (1, 1, 'Xiaomi MI TV A2 43'), 
  (2, 1, 'Samsung UE65CU7100UXRU'), 
  (3, 2, 'Apple iPhone 15');

INSERT INTO template_property(id, template_id, title)
VALUES 
  (1, 1, 'Диагональ'), 
  (2, 1, 'Разрешение'), 
  (3, 2, 'Цвет'),
  (4, 2, 'Ширина');

INSERT INTO product_property_value(id, product_id, property_id, value)
VALUES 
  (1, 1, 1, '43"'), 
  (2, 1, 2, '4K UltraHD, 3840x2160'), 
  (3, 3, 3, 'черный'),
  (4, 3, 4, '71.6мм');

INSERT 0 3



INSERT 0 3



INSERT 0 4



INSERT 0 4


SELECT DISTINCT p.id, p.title
FROM product_property_value AS ppv
INNER JOIN product AS p
ON p.id = ppv.product_id
WHERE ppv.property_id = 3 AND value = 'черный'


id
title


3
Apple iPhone 15



SELECT 1


WITH choosen_properties AS (
  SELECT DISTINCT id, filter.value FROM template_property AS tp
  INNER JOIN (
      VALUES ('Цвет', 'черный'), ('Ширина', '71.6мм')
  ) AS filter(property, value)
  ON tp.title = filter.property
)

SELECT DISTINCT p.id, p.title
FROM product_property_value AS ppv
INNER JOIN choosen_properties
ON choosen_properties.id = ppv.property_id
AND choosen_properties.value = ppv.value
INNER JOIN product AS p
ON p.id = ppv.product_id


id
title


3
Apple iPhone 15



SELECT 1