cancel
Showing results for 
Search instead for 
Did you mean: 

create collection for direct sql query

create collection for direct sql query

Hello Every One

 

Please any body help to create a sql query to magento collection or simple convert the below sql  query to magento query.

 

$query = "SELECT name AS name, NULL as productname
		FROM `catalog_product_flat_1` a
		WHERE (
		a.name LIKE '%".$filter."%'
		)
		UNION ALL 
		SELECT NULL , productname AS productname
		FROM `Offlinedb` b
		WHERE (
		b.productname LIKE '%".$filter."%'
		)"; 		

Thanks 

1 REPLY

Re: create collection for direct sql query

<?php
class Webkul_Productsearch_Block_Productsearch extends Mage_Catalog_Block_Product_Abstract{

	public function __construct()
    {
        parent::__construct();
		
       // $collectionfetch = Mage::getModel('Offlinedb/Offlinedb')->getCollection();

				
		if ($filter = $this->getRequest()->getParam('q')) {				
	    $resource = Mage::getSingleton('core/resource');
	    $readConnection = $resource->getConnection('core_read');
    	$writeConnection = $resource->getConnection('core_write');
        $offlinedb   = $resource->getTableName('Offlinedb');
        $prodTable   = $resource->getTableName('catalog_product_flat_1');	
		
		$query = "SELECT name AS name, NULL as productname
		FROM `catalog_product_flat_1` a
		WHERE (
		a.name LIKE '%".$filter."%'
		)
		UNION ALL 
		SELECT NULL , productname AS productname
		FROM `Offlinedb` b
		WHERE (
		b.productname LIKE '%".$filter."%'
		)"; 		
		//$query = "SELECT name from ".$prodTable." where name like '%".$filter."%' ";	
		 $collection = $readConnection->fetchall($query);
			
		}

        $this->setCollection($collection);
    } 
    protected function _prepareLayout() {
         parent::_prepareLayout(); 
        $pager = $this->getLayout()->createBlock('page/html_pager', 'custom.pager');
        $pager->setAvailableLimit(array(10=>10,20=>20,30=>30,'all'=>'all'));
        $pager->setCollection($this->getCollection());
        $this->setChild('pager', $pager);
        $this->getCollection()->load();
        return $this;
    } 
	
    public function getPagerHtml()
    {
        return $this->getChildHtml('pager');
    }
}

Here is the total code..

 

And please check the error below.

 

Fatal error: Call to a member function setCurPage() on a non-object in app/code/core/Mage/Page/Block/Html/Pager.php on line 140

But  when I use the single collection 

 

$collectionfetch = Mage::getModel('Offlinedb/Offlinedb')->getCollection();

its working fine but I need to search from two tables ..

 

Please help ...

 

Thanks