0

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))
            ->addFieldToFilter('status',
                array(
                    'nin' => array(
                        Mage_Downloadable_Model_Link_Purchased_Item::LINK_STATUS_PENDING_PAYMENT,
                        Mage_Downloadable_Model_Link_Purchased_Item::LINK_STATUS_PAYMENT_REVIEW
                    )
                )
            );

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');
$purchasedItems->getSelect()
            ->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? – oleksii.svarychevskyi Mar 7 '14 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 '14 at 19:20
0

'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:

$purchasedItems->getSelect()
->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 '14 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 '14 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 '14 at 20:00
  • This means the collection is empty. Try removing the filters (addFieldToFilter()) and then try again – Alex Dinca Mar 7 '14 at 20:05
  • Still get nothing after removing the filters – ZeLoubs Mar 7 '14 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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