I'm working on a store that only has downloadable products in it. I need to get each customer's downloadable products and be able to sort them by product name and sku. My current code works in retrieving all the download links.

$purchasedItems = Mage::getResourceModel('downloadable/link_purchased_item_collection')
            ->addFieldToFilter('purchased_id', array('in' => $purchasedIds))
                    'nin' => array(

However, this does not return the product name nor the sku. So I am attempting a join on the collection to the catalog_product_flat table.

$productTableName = Mage::getSingleton('core/resource')->getTableName('catalog/product_flat');
            ->join(array('products' => $productTableName), 'main_table.product_id = products.entity_id', array('products.*'));

Doing a var_dump($purchasedItems) returns a Mage_Downloadable_Model_Resource_Link_Purchased_Item_Collection Object. But I cannot iterate over the $purchasedItems.

Any help would be greatly appreciated.

  • Why you cannot iterate? Try to get sql $purchasedItems->getSelect()->__toString() and execute it directly in mysql. What will be the result? Mar 7, 2014 at 18:54
  • I don't have access to the server unforunately. but dumping $purchasedItems->getSelect()->__toString() returns SELECT main_table.*, products.* FROM m_downloadable_link_purchased_item` AS main_table INNER JOIN m_catalog_product_flat AS products ON main_table.product_id = products.entity_id WHERE (purchased_id IN('2003877', '2003880', '2003883', '2003886', '2003871', '2003874', '2003865', '2003838', '2003841', '2003844', '2003847', '2003850', '2003853', '2003856', '2003859', '2003829', '2003832', '2003835')) AND (status NOT IN('pending_payment', 'payment_review'))`
    – ZeLoubs
    Mar 7, 2014 at 19:20

1 Answer 1


'table_alias' doesn't reference any table in your query. For the flat product table, your defined alias is 'products'

Thus, your join code should be like this:

->join(array('products' => $productTableName), 'main_table.product_id = products.entity_id', array('products.*'));

Also, be aware that if you create additional store views you will have multiple flat product tables: 'catalog_product_flat_1' 'catalog_product_flat_2' where 1 and 2 are store codes

  • I have updated my question and replaced the table_alias with products, but I still cannot iterate over them
    – ZeLoubs
    Mar 7, 2014 at 19:39
  • Do you get an error when you try to iterate over the items? What do you get if you run "echo $purchasedItems->count()" ?
    – Alex Dinca
    Mar 7, 2014 at 19:54
  • I don't get any errors when trying to iterate, just no results. I have tried foreach ($purchasedItems as $item) { Mage::log($item); } which doesn't return anything. Also 'Mage::log($purchasedItems->count());` does not return anything either. But Mage::log($purchasedItems) returns a Mage_Downloadable_Model_Resource_Link_Purchased_Item_Collection object
    – ZeLoubs
    Mar 7, 2014 at 20:00
  • This means the collection is empty. Try removing the filters (addFieldToFilter()) and then try again
    – Alex Dinca
    Mar 7, 2014 at 20:05
  • Still get nothing after removing the filters
    – ZeLoubs
    Mar 7, 2014 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.