I would like to do following on 1.9.0.1:
Getting 12 random upsell-products from same category as current product.
They have to be saleable.
They shouldnt be in related-products already.
They shouldnt be in cart.
And if there are not 12 in that category fill the rest with products from parent category.
I think this is getting complex but I can do it. Just not sure if I can do it good 🙂
My Question is do I have to worry about performance with that select complexity? What do you mean?
Code examples would be great that I can compare them.
Best Answer
I recently implemented something very similar and will share the relevant code below. But first some comments about performance, since that was the question:
General thoughts on performance for your requirements:
ORDER BY RAND()
because it results in a resource intensive temp table copy. It has to load all results into a temporary table, assign a random number to each row and then sort without any index. Instead we retrieve all candidate ids (this is faster and the amount of data is managable even for large catalogs), pick some randomly and retrieve these rows directly. You can read about it in detail in my blog: http://www.schmengler-se.de/en/2015/09/show-random-products-in-magento-you-are-doing-it-wrong/Specific performance problems with your solution
addAttributeToSelect('*')
with EAV collections, only select what you neednew Zend_Db_Expr('FIELD(category_id, ' . implode(',', $_cat_order).')'))
can efficiently use the MySQL indexesComplete Solution
This is an observer for the
catalog_product_upsell
event. It uses the product collection provided by the product's category because I didn't need to fall back to the parent, but I'm sure you can adjust it.Some parts that I'd like to highlight:
This excludes the product itself and the products that are already manually defined as upsell products (I leave them and only fill the blanks with random products)
This prepares the product collection to load the necessary data to display prices, the product link and any attributes configured as "used in product listing", but not more.