Magento: Fetch data from custom table

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;
}
?>