Magento: Modify Collection To Include Comma Separated Values From Another Table

It was rather difficult to come up with a title for this post, so I’m not sure that it is completely accurate. But, I have been working on a module, and I needed to modify the catalog/product collection to add a column that contains comma separated skus of the related products associated to each product.

First step is to get the product collection,

$collection = Mage::getModel('catalog/product')->getCollection();

Now we need to modify the collection to add a new select column. The mysql is also important here. You’ll see that I’m using GROUP_CONCAT and DISTINCT, and towards the bottom specifying to group by ‘e.entity_id’. I’m not going to dive into why this is necessary or how it all works. You’ll be better off visiting the mysql documentation on those.

$collection = Mage::getModel('catalog/product')->getCollection();
 
$collection->getSelect()
    ->columns('GROUP_CONCAT(DISTINCT (SELECT sku FROM catalog_product_entity WHERE entity_id = r.linked_product_id) SEPARATOR \', \') AS related_skus')
    ->joinLeft(array('r' => 'catalog_product_link'), 'r.product_id = e.entity_id AND r.link_type_id = 1')
    ->group('e.entity_id');

The result of this is an additional field in the collection called ‘related_skus’ that is a comma-space separated list of skus that are related products for each product in the collection.

If you want to log the actual query that is generated by this, simply add this line below the code above:

Mage::log($collection->getSelect()->__toString());

Hopefully this helps you in some way!

This entry was posted in Magento, MySQL. Bookmark the permalink.

One Response to Magento: Modify Collection To Include Comma Separated Values From Another Table

  1. iain b says:

    Exactly what i needed.. thanks!
    P.S. the title works