Sample Header Ad - 728x90

Products variants in EAV

1 vote
1 answer
214 views
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.*
Asked by kicaj (123 rep)
Jan 25, 2023, 10:31 AM
Last activity: Jan 25, 2023, 01:35 PM