How to fetch data from custom table in magento

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')-&gt;getCollection();	
			$collection-&gt;addFieldToFilter('source','product');
			$collection-&gt;addFieldToFilter('recipient_email',Array('neq'=&gt;''));
			$expr = 'COUNT(points_id)';
			$collection-&gt;setOrder($expr,'DESC');		
			$collection-&gt;setGroup('source_id');		
			$collection-&gt;addProductData();
?>

2)
protected function _prepareCollection() 
		{
			$read = Mage::getSingleton('core/resource')-&gt;getConnection('core_read');
				
			$firstname  = Mage::getResourceSingleton('customer/customer')-&gt;getAttribute('firstname');     
			$middlename = Mage::getResourceSingleton('customer/customer')-&gt;getAttribute('middlename'); 
		    $lastname   = Mage::getResourceSingleton('customer/customer')-&gt;getAttribute('lastname'); 
			$email      = Mage::getResourceSingleton('customer/customer')-&gt;getAttribute('email'); 
					
			$expr = 'CONCAT(customer_firstname_table.value,CONCAT(&quot; &quot;,IF(customer_middlename_table.value IS NOT NULL, customer_middlename_table.value,&quot;&quot;)),&quot; &quot;,customer_lastname_table.value)';
	 	
			$collection = Mage::getModel('rewardpoints/points')-&gt;getCollection();

			$collection-&gt;getSelect()
		        -&gt;joinLeft(
		            array('customer_lastname_table' =&gt; Mage::getSingleton('core/resource')-&gt;getTableName($lastname-&gt;getBackend()-&gt;getTable())),
		            'customer_lastname_table.entity_id = main_table.customer_id
		             AND customer_lastname_table.attribute_id = '.(int) $lastname-&gt;getAttributeId() . '
		             ',
		            array('lastname'=&gt;'value')
		         )
				 -&gt;joinLeft(
		            array('customer_middlename_table' =&gt;Mage::getSingleton('core/resource')-&gt;getTableName($middlename-&gt;getBackend()-&gt;getTable())),
		            'customer_middlename_table.entity_id = main_table.customer_id
		             AND customer_middlename_table.attribute_id = '.(int) $middlename-&gt;getAttributeId() . '
		             ',
		            array('middle'=&gt;'value')
		         )
		         -&gt;joinLeft(
		            array('customer_firstname_table' =&gt;Mage::getSingleton('core/resource')-&gt;getTableName($firstname-&gt;getBackend()-&gt;getTable())),
		            'customer_firstname_table.entity_id = main_table.customer_id
		             AND customer_firstname_table.attribute_id = '.(int) $firstname-&gt;getAttributeId() . '
		             ',
		            array('firstname'=&gt;'value')
		         )
				  -&gt;joinLeft(
		            array('customer_email_table' =&gt; Mage::getSingleton('core/resource')-&gt;getTableName('customer_entity')),
		            'customer_email_table.entity_id = main_table.customer_id',
		            array('email'=&gt;'email')
		         );
			
			$collection-&gt;getSelect()-&gt;from(null, array('name'=&gt;$expr));	
			$collection-&gt;getSelect()-&gt;from(null, array('source'=&gt;' CONCAT(UCASE(SUBSTRING(source, 1, 1)),LOWER(SUBSTRING(source, 2)))'));			
			
		    //$collection-&gt;getSelect()-&gt;joinLeft( array('recipients'=&gt;Mage::getSingleton('core/resource')-&gt;getTableName('reward_points_recipient')), 'recipients.recipient_id= main_table.recipient_id',array('recipient_email'=&gt;'recipient_email','recipient_name'=&gt;'recipient_name')); 
			
			$collection-&gt;addNameToJoinFields($expr);
			
			$this-&gt;setCollection($collection);
			return parent::_prepareCollection();
			
		}
3)
protected function _prepareCollection()
		{
        $store = $this-&gt;_getStore();
		
		$collection = Mage::getModel('rewardpoints/referral')-&gt;getCollection();	
			$expr = 'COUNT(referral_id)';
			$collection-&gt;setOrder($expr,'DESC');		
			$collection-&gt;setGroup('product_id');		
			$collection-&gt;addProductData();
			
        $this-&gt;setCollection($collection);
		
        parent::_prepareCollection();

        return $this;
		}

Advertisement

Go to Smartblog Theme Options -> Ad Management to enter your ad code (300x250)

19 Responses to “How to fetch data from custom table in magento”

  1. March 29, 2011 at 5:35 pm #

    Thank you for the excellent post afturu. Very carefully examined and found many useful things for themselves.

    • June 13, 2011 at 3:55 pm #

      Very true! Makes a cnahge to see someone spell it out like that. :)

    • June 14, 2011 at 3:33 pm #

      That’s way more clever than I was exepcitng. Thanks!

      • Vishal Sanwar
        June 16, 2011 at 4:53 am #

        but in which place ,we have to add the table nameplz clear all thing, thanx

        • sanjeev
          June 16, 2011 at 11:40 am #

          You should define your table name in resource model which is call similar as rewardpoints/points [Namespace/model name] in above example.

          If you want to query or update or insert in your custom table then you can do in simple way as follows

          // fetch write database connection that is used in Mage_Core module
          $write = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

          // now $write is an instance of Zend_Db_Adapter_Abstract
          $write->query(“SELECT * FROM TABLE_NAME”);

        • June 25, 2011 at 6:50 am #

          A million thanks for posting this infomraoitn.

  2. April 23, 2011 at 1:55 pm #

    Cool blog! Thank you for creating it. Keep working that way.

    • June 13, 2011 at 10:15 am #

      At last, someone comes up with the “right” asnewr!

      • June 25, 2011 at 7:06 pm #

        And I was just wdonreing about that too!

    • June 14, 2011 at 4:05 pm #

      Good point. I hadn’t thoguht about it quite that way. :)

  3. June 12, 2011 at 11:32 pm #

    Hahahaha. I’m not too brhigt today. Great post!

  4. June 14, 2011 at 1:47 pm #

    Real brain power on dpilsay. Thanks for that answer!

  5. Vishal Sanwar
    June 16, 2011 at 4:54 am #

    plz write complete doc of these,bcoz it is more helpful fo other or who is new in magento

  6. June 16, 2011 at 7:52 am #

    Hey there! This is my 1st comment here so I just wanted to give a quick shout out and tell you I really enjoy reading through your posts. Can you recommend any other blogs/websites/forums that go over the same topics? Thank you!

  7. June 18, 2011 at 4:06 am #

    Incredible! This blog looks exactly like my old one! It’s on a entirely different topic but it has pretty much the same layout and design. Wonderful choice of colors!

  8. June 19, 2011 at 4:09 am #

    I’d come to play ball with you one this subject. Which is not something I typically do! I enjoy reading a post that will make people think. Also, thanks for allowing me to comment!

    • June 25, 2011 at 6:11 pm #

      Many many qulaity points there.

  9. June 25, 2011 at 10:00 am #

    Hello.This post was really fascinating, particularly since I was looking for thoughts on this matter last Tuesday.

Leave a Comment