CODEWITHSUNDEEP
magentojoincollections
ZeLoubs
ZeLoubs

Reputation: 215

Magento Join Collection Tables

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('table_alias.*'));

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.

Upvotes: 1

Views: 8939

Answers (1)

Dharmesh Thanki
Dharmesh Thanki

Reputation: 420

Please have a look for joining magento custom, I am using it in my project and it is working perfectly.

Syntax

$collection = Mage::getModel('module/model_name')->getCollection();

$collection->getSelect()->join(Mage::getConfig()->getTablePrefix().'table_name_for_join', 'main_table.your_table_field ='.Mage::getConfig()->getTablePrefix().'table_name_for_join.join_table_field',array('field_name_you_want_to_fetch_from_db'));

Working Query Example

$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->join(Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar', 'main_table.products_id ='.Mage::getConfig()->getTablePrefix().'catalog_product_entity_varchar.entity_id',array('value'));

Hope this will work for you as well!

Cheers!

Upvotes: 4

Related Questions