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 (for example Blue/1kg, Green/1kg, etc).
By default, Magento would display it as a single (configurable product) on a category page, and customers 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 an appropriate color image (this is pulled from a simple product). Upon clicking on any of them, the same parent product would be displayed on PDP.
What I tried so far:
Approach #1:
We know that on the 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:
But the problem is that I can't instantiate a collection from it since it would try to add items with an ID that already exists (entity_id = 311 for example).
Approach #2:
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 the idea, how this could be done in a good, less-hacky way?
I would appreciate any ideas if someone is willing to share.
