Magento Left Join Table Query

You are about to get a collection in Magento System involves in some tables

Posted on November 12, 2015 in Magento

Recently, there is a request from my client about getting some data from Magento platform involving some tables, like a tracking table contains a column with “email”, which has been used to join with Customer table to get the customer id and then using this id to left join customer attribute table ‘customer_entity_varchar’ with specific attribute_id. Here is the working code to fulfill the task:

$collection = Mage::getModel("refferer/reftable")->getCollection()->addFieldToFilter('ip_address', array('nin'=>array(''))); 
                    ->joinLeft(array('ce3'=>'customer_entity'), ' =', 'ce3.entity_id')
                    ->joinLeft(array('a'=>'customer_entity_varchar'), 'ce3.entity_id = a.entity_id and a.attribute_id = 202', array('company_name'=>'a.value'))
                    ->joinLeft(array('b'=>'customer_entity_varchar'), 'ce3.entity_id = b.entity_id and b.attribute_id = 201', array('company_no'=>'b.value'))
                    ->joinLeft(array('c'=>'customer_entity_varchar'), 'ce3.entity_id = c.entity_id and c.attribute_id = 206', array('salerep'=>'c.value'))
                    ->joinLeft(array('d'=>'customer_entity_varchar'), 'ce3.entity_id = d.entity_id and c.attribute_id = 203', array('phone'=>'d.value'));

After getting this collection, you can use it to bind some grids in the backend, like

return parent::_prepareCollection();

comments powered by Disqus