Products variants in EAV

Question

I preapre small EAV table for product variants.

CREATE TABLE products (
  id int auto_increment,
  parent_id int default null,
  name varchar(30) not null,
  PRIMARY KEY (id)
);

CREATE TABLE products_eav (
  id int auto_increment,
  product_id int,
  attr varchar(30),
  val varchar(30),
  PRIMARY KEY (id)
);

I would like return products grouped by parent_id (always) and depending on the selected attribute, sometimes "products red", sometimes "products red cotton", etc.

SELECT 
    MAX(id) AS id
    , parent_id
    , color
    , material
    , COUNT(id) 
FROM (
  SELECT 
    products.id
    , products.parent_id
    , colors.val AS color
    , materials.val AS material
  FROM products
  JOIN products_eav AS colors ON products.id = colors.product_id
  JOIN products_eav AS materials ON products.id = materials.product_id
  WHERE
    products.parent_id IS NOT NULL
    AND colors.attr = 'Colors'
    AND materials.attr = 'Material'
) AS product_variants
GROUP BY
    parent_id
    , color
    , material

When in table products_eav each product has the same attributes everything works fine, but when just one product has just one attribute less and query wants display each products grouped in two attributes this product is ignored - it's clear.

Look for example: https://www.db-fiddle.com/f/hyhUMMtidU1vxnmCiZXZYk/5 Product "Pink pants" hasn't material attribute and is ignored in results.

How to change the query to return "pants" in results (I mean, one conditions is enough).
Imagine this is listing products page: When user selected filter like colors and materials, he should see pink pants too.

Answer 1

It's because you're using INNER JOINs in your subquery, and you need outer joins, particularly LEFT JOINs instead, so the products list doesn't get filtered there. (You'll also need to move the predicates for each attribute out of the WHERE clause in the subquery, and into the LEFT JOIN clauses.) Then in the outer query you can add a WHERE clause to check that at least one of the attributes isn't NULL, to ensure you only return products with at least one matched attribute, like so:

SELECT 
    MAX(id) AS id
    , parent_id
    , color
    , material
    , COUNT(id) 
FROM (
  SELECT 
    products.id
    , products.parent_id
    , colors.val AS color
    , materials.val AS material
  FROM products
  LEFT JOIN products_eav AS colors
      ON products.id = colors.product_id
      AND colors.attr = 'Colors'
  LEFT JOIN products_eav AS materials 
      ON products.id = materials.product_id
      AND materials.attr = 'Material'
  WHERE
    products.parent_id IS NOT NULL
) AS product_variants
WHERE color IS NOT NULL
    OR material IS NOT NULL
GROUP BY
    parent_id
    , color
    , material