Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Make collection using custom query
 
vishal_2811
Jr. Member
 
Avatar
Total Posts:  20
Joined:  2011-04-17
 

Hi All,
After digging a lot and googling I found a solution to use custom query to make collection:-

Placeholders in my case:

<Name_space> = Acme
<Module> = News
<Model> = News

Step 1:
Just navigate to your module at below path
app\code\local\<Namespace>\<Module>\Model\Mysql4\<Model>
ans create a file “Collection.php” with the following code (if already exist just override the methods “getData” and “load” as suggested into this)

class <Namespace>_<Module>_Model_Mysql4_<Model>_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract{
    
public function _construct()
    
{
        parent
::_construct();
        
$this->_init("<Module>/<Model>");
    
}
    
    
public function getData($select=null){
        
if ($this->_data === null{
            $this
->_renderFilters()
                 ->
_renderOrders()
                 ->
_renderLimit();
            if(!
is_null($select)){
                $this
->_select $select;
            
}
            $this
->_data $this->_fetchAll($this->_select);
            
$this->_afterLoadData();
        
}
        
return $this->_data;
    
}
    
    
public function load($select=null$printQuery false$logQuery false){
        
if ($this->isLoaded()) {
            
return $this;
        
}

        $this
->_beforeLoad();

        
$this->_renderFilters()
             ->
_renderOrders()
             ->
_renderLimit();

        
$this->printLogQuery($printQuery$logQuery);

        
$data $this->getData($select);
        
$this->resetData();

        if (
is_array($data)) {
            
foreach ($data as $row{
                $item 
$this->getNewEmptyItem();
                if (
$this->getIdFieldName()) {
                    $item
->setIdFieldName($this->getIdFieldName());
                
}
                $item
->addData($row);
                
$this->addItem($item);
            
}
        }

        $this
->_setIsLoaded();
        
$this->_afterLoad();
        return 
$this;
    
}
}

Step 2:
After Step1 I used to prepare collection (in admin grid) from custom query as below in the file “Grid.php” at path
app\code\local\<Namespace>\<Module>\Block\Adminhtml\<Model>

protected function _prepareCollection(){
    $select 
'select * from <Mytable> where <Mycondition>';
    
$newsColl = new Acme_News_Model_Mysql4_News_Collection();
    
$newsColl->load($select);
    
/* just to see what data is coming
    print '<pre>';
        print_r($newsColl->getData());
    print '</pre>';
    foreach($newsColl as $records){
        print '<br>'.$records->getItemTitle();
    }
    */
    
$this->setCollection($newsColl);
    return 
parent::_prepareCollection(); 
}

That’s all.  smile

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top