Fetching data from custom table in magento is quite difficult because its data store in entity attribute value (EAV) model therefore most of the table structure are in EAV model. So joining one table with other tables is different from other database system. Here is a query example of sample table which helps you to use join, select, group, filter data from custom table .
Here are some examples of code which helps you to retrieve data from custom table using join, select.
1)
<?php $collection = Mage::getModel('rewardpoints/points')->getCollection(); $collection->addFieldToFilter('source','product'); $collection->addFieldToFilter('recipient_email',Array('neq'=>'')); $expr = 'COUNT(points_id)'; $collection->setOrder($expr,'DESC'); $collection->setGroup('source_id'); $collection->addProductData(); ?>
2)
<?php protected function _prepareCollection() { $read = Mage::getSingleton('core/resource')->getConnection('core_read'); $firstname = Mage::getResourceSingleton('customer/customer')->getAttribute('firstname'); $middlename = Mage::getResourceSingleton('customer/customer')->getAttribute('middlename'); $lastname = Mage::getResourceSingleton('customer/customer')->getAttribute('lastname'); $email = Mage::getResourceSingleton('customer/customer')->getAttribute('email'); $expr = 'CONCAT(customer_firstname_table.value,CONCAT(" ",IF(customer_middlename_table.value IS NOT NULL, customer_middlename_table.value,""))," ",customer_lastname_table.value)'; $collection = Mage::getModel('rewardpoints/points')->getCollection(); $collection->getSelect() ->joinLeft( array('customer_lastname_table' => Mage::getSingleton('core/resource')->getTableName($lastname->getBackend()->getTable())), 'customer_lastname_table.entity_id = main_table.customer_id AND customer_lastname_table.attribute_id = '.(int) $lastname->getAttributeId() . ' ', array('lastname'=>'value') ) ->joinLeft( array('customer_middlename_table' =>Mage::getSingleton('core/resource')->getTableName($middlename->getBackend()->getTable())), 'customer_middlename_table.entity_id = main_table.customer_id AND customer_middlename_table.attribute_id = '.(int) $middlename->getAttributeId() . ' ', array('middle'=>'value') ) ->joinLeft( array('customer_firstname_table' =>Mage::getSingleton('core/resource')->getTableName($firstname->getBackend()->getTable())), 'customer_firstname_table.entity_id = main_table.customer_id AND customer_firstname_table.attribute_id = '.(int) $firstname->getAttributeId() . ' ', array('firstname'=>'value') ) ->joinLeft( array('customer_email_table' => Mage::getSingleton('core/resource')->getTableName('customer_entity')), 'customer_email_table.entity_id = main_table.customer_id', array('email'=>'email') ); $collection->getSelect()->from(null, array('name'=>$expr)); $collection->getSelect()->from(null, array('source'=>' CONCAT(UCASE(SUBSTRING(source, 1, 1)),LOWER(SUBSTRING(source, 2)))')); $collection->addNameToJoinFields($expr); $this->setCollection($collection); return parent::_prepareCollection(); } ?>
3)
<?php protected function _prepareCollection() { $store = $this->_getStore(); $collection = Mage::getModel('rewardpoints/referral')->getCollection(); $expr = 'COUNT(referral_id)'; $collection->setOrder($expr,'DESC'); $collection->setGroup('product_id'); $collection->addProductData(); $this->setCollection($collection); parent::_prepareCollection(); return $this; } ?>