计算产品过滤器中每个项目的产品

I need help with writing SQL (or logical procedure) to counting products for each item in product filter.

Database structure:

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


attributes
----------
id|title


attributes_values
---------------------
id|value|attribute_id


products_attributes
-------------------
product_id|attributes_value_id

I have sidebar filter with properties and I need to get the count of products for each property (row in filter). The problem is that count should be already limited by already selected property. So when I have selected any property, all others property must counting products with the selected property too.

Eg: I have two properties, color (blue, white) and material (metal, wood). When i have selected blue color, i need to calculate count of products for metal in blue color and for wood in blue color - the counted products must match all selected properties.

It is possible to use any joining table or external service, but I don't known how to do it, how change structure to do it works.

DBFiddle: https://www.db-fiddle.com/f/mMeUzm96xb9ZMHZonEktcD/1

Thanks for any idea.

Well the first step is to combine these tables attributes, attributes_values and products_attributes into something proper like product_meta or product_options or even product_attributes.

Each product only has a One to many relationship with attributes. For example a product can have many attributes, but an attribute belongs to only one product. Please note this still allows you to have duplicate attributes. For example there is no reason product 1 cannot have 2 color attributes.

To be sure you can "Normalize" it to the level you have but it's unnecessary and will just complicate the SQL and cause all kinds of headaches latter on. So instead of what? 2 Many to Many relationships and a One to Many, all you really need is the One to Many

So we will start there:

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

product_attributes
id|product_id|attr_key|attr_value

Now for your query

SELECT 
   COUNT(p.id)
FROM
   products p
JOIN
   product_attributes pa ON p.id = pa.product_id
WHERE
   (pa.attr_key="color" AND pa.attr_value="blue")

This will give you the count of all blue products (regardless of material). Pretty strait forward, but not exactly what we need.

To add the material in is a bit more difficult, but it can be done by Joining again on the attributes (And grouping on the value etc.) Like this:

  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"

Basically we are SELECTING all products that have an attribute of "color" blue, but also have an attribute of "material" {something}.

When you use a Key Value store like this and want to access two values from that same table this will require the extra join on the table. So if you have a very complicated schema with 3 tables just for these attributes you have to reproduce all those secondary joins multiple times. It's best to keep it simple.

Please also see this DB fiddle

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');

The results of the above queries are 1 and 3 (note I added a 3rd material) with just 2 you get the correct count. This was to show that you can have more then just the 2. If you want to restrict it to just two out of 3, do something like this:

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

You can verify this a little by selecting without the attr values, which gives 6 which is metal/blue,metal/white and wood/blue,wood/white and (in my example) plastic/blue,plastic/white. Which is all the possible combinations of those attributes.

One last note is you could put a Unique index on attr_key and attr_value as a compound key, this would prevent duplicate key/value pairs.