An example to join a table to a Magento collection

Here is an example where I join sales_flat_order table to an order payment collection.

//Alias to distinct fields between tables
$saleOrderTableAlias = 's_o';

//Order payment model
$orderPayment = Mage::getModel('sales/order_payment');

//Order payment collection
$collection = $orderPayment->getResourceCollection();

//Table join
$collection->join(
    array($saleOrderTableAlias => 'sales/order'),
    'parent_id=s_o.entity_id'
);

To include only the fields you want, you can add an array to join function parameters:

//Table join
$collection->join(
    array($saleOrderTableAlias => 'sales/order'),
    'parent_id=s_o.entity_id',
    arrey('increment_id')
);

Also, you can add conditions thanks to Zend_Db_Select object:

$select = $collection->getSelect();
$select->where('s_o.status like ?','pending');

Many functions are available to build your requests. Do not hesitate to search those in the extended classes, like Zend_Db_Select or Mage_Core_Model_Resource_Db_Collection_Abstract.

Tweet about this on Twitter0Share on Facebook0Share on Google+0

Michael

Originally trained as a sound engineer, I reinvented myself in computer development. In 2010, I got my professional degree as a developer in computer science and I I've started studying Magento framework. I quickly developed my skills in that area at Brand Online Commerce web agency. Today, I work at Soon Agency on Magento ecommerce websites.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>