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.