подсчет продуктов для каждого элемента в фильтре продуктов

Ну, первый шаг - объединить эти таблицы attributes, attributes_values и products_attributes во что-то правильное, например, product_meta, product_options или даже product_attributes.

Каждый продукт имеет отношение «один ко многим» только с атрибутами. Например, продукт может иметь много атрибутов, но атрибут принадлежит только одному продукту. Обратите внимание, что это по-прежнему позволяет иметь дубликаты атрибутов. Например, нет причины, по которой товар 1 не может иметь 2 цветовых атрибута.

Чтобы быть уверенным, что вы можете «нормализовать» его до уровня, который у вас есть, но это не нужно и просто усложнит SQL и вызовет всевозможные головные боли. Так вместо чего? 2 Отношения «многие ко многим» и «один ко многим», все, что вам действительно нужно, это «один ко многим»

. Итак, мы начнем с этого:

products
--------
id|title

product_attributes
id|product_id|attr_key|attr_value

Теперь по вашему запросу

Это даст вам счет всех синих продуктов (независимо от материала). Довольно прямолинейно, но не совсем то, что нам нужно.

Добавить материал в это немного сложнее, но это можно сделать, снова присоединившись к атрибутам (и сгруппировав по значению и т. Д.), Например:

  SELECT 
     COUNT(p.id)
  FROM
     products p
  JOIN
     #instead of one table, with your schema you would need 3 (apx) joins here
     product_attributes pa ON p.id = pa.product_id
  JOIN
     #you would also need 3 joins here
     product_attributes pa1 ON p.id = pa1.product_id 
  WHERE
     pa.attr_key="color" AND pa.attr_value="blue"
     AND 
     pa1.attr_key="material"

По сути, мы выбираем все товары, которые имеют атрибут "color" синий, но также имеют атрибут "material" {что-то}.

Когда вы используете хранилище значений ключей, подобное этому, и хотите получить доступ к двум значениям из той же таблицы, это потребует дополнительного объединения в таблице. Поэтому, если у вас очень сложная схема с 3 таблицами только для этих атрибутов, вам придется воспроизводить все эти вторичные объединения несколько раз. Лучше быть проще.

Также см. Эту скрипту БД

https://www.db-fiddle.com/f/mdVypFwY7WsfHcgkasR7gE/1

CREATE TABLE products(
   id INT(10),
    title VARCHAR(250)
 );

 INSERT INTO products (id,title)VALUES(1,'foo');
 INSERT INTO products (id,title)VALUES(2,'bar');

 CREATE TABLE product_attributes(
   id INT(10),
   product_id INT(10),
   attr_key VARCHAR(250),
   attr_value VARCHAR(250)
 );

  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(1,1,'color', 'blue');
  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(2,1,'color', 'white');
  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(3,1,'material', 'metal');
  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(4,1,'material', 'wood');

  #say we also have plastic
  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(4,1,'material', 'plastic');

  INSERT INTO product_attributes (id,product_id,attr_key,attr_value)VALUES(1,2,'color', 'white');

Результаты вышеупомянутые запросы - 1 и 3 (заметьте, я добавил третий материал), когда всего 2, вы получите правильный счет. Это должно было показать, что вы можете иметь больше, чем просто 2. Если вы хотите ограничить его только двумя из 3, сделайте что-то вроде этого:

   WHERE
     pa.attr_key="color" AND pa.attr_value="blue"
     AND 
     pa1.attr_key="material" AND pa1.attr_value IN("wood", "metal")

Вы можете немного проверить это, выбрав без значения attr, который дает 6, который является metal/blue, metal/white и wood/blue, wood/white и (в моем примере) plastic/blue, plastic/white. Это все возможные комбинации этих атрибутов.

И последнее замечание: вы можете поместить уникальный индекс на attr_key и attr_value как составной ключ, это предотвратит дублирование пар ключ / значение.