Imagine that I have a configurable product with the following options:
- color: blue, green, orange, pink
- size: 1kg, 2kg, 3kg, 4kg
and that I have simple products having some of these attribute combinations (example Blue/1kg, Green/1kg, etc).
By default, Magento would display it as a single (configurable product) on a category page, and customer can select desired options (color/size) from PDP.
What I am trying to achieve is to display all available colors on category listing as a separate product (in this case 4 products should be displayed as I have 4 different colors in stock). The products should be displayed with appropriate color image (this is pulled from simple product). Upon clicking on any of them, the same parent product would be displayed on PDP.
What I tried so far:
We know that on category page, Magento executes something like this (for simplicity reasons, I excluded category/price/stock joins):
select * from catalog_product_flat_1 main_table where main_table.visibility IN (2,4);
If we want to add color variations, we need to do modify product collection to be like:
select main_table.sku, child_table.color_value as color_variation, main_table.* from catalog_product_flat_1 main_table left join catalog_product_super_link cpsl on cpsl.parent_id = main_table.entity_id left join catalog_product_flat_1 child_table on cpsl.product_id = child_table.entity_id where main_table.visibility IN (2,4) group by main_table.entity_id, child_table.color_value;
This gives me exactly what I wanted:
My next approach that kind of works, but I am not sure what consequences I may have if I proceed with it.
The idea is to configure simple (children) products to be visible in catalog/search, and group them by color (note how I had to group by a unique string UUID() if color is not set, since I will have products without color too):
select * from catalog_product_flat_1 main_table where main_table.visibility IN (2,4) GROUP BY color, IF(color IS NULL, UUID(), color)
This works, but honestly I don't like the solution that much.
So my question is more about idea, how this could be done in a good, less-hacky way?
I would appreciate any ideas if someone is willing to share.