Filter Collection using Mysql4 Model

Question

Block file

<?php

class Namespace_Modulename_Block_Modulename extends Mage_Core_Block_Template{


        public function __construct()
    {
        parent::__construct();
        $collection = Mage::getModel('module/model')->getCollection();
        $this->setCollection($collection);
    }

    protected function _prepareLayout()
    {
        parent::_prepareLayout();

        $pager = $this->getLayout()->createBlock('page/html_pager', 'mymodule.pager');
        $pager->setAvailableLimit(array(5=>5,10=>10,20=>20,'all'=>'all'));
        $pager->setCollection($this->getCollection());                
        $this->setChild('pager', $pager);
        $this->getCollection()->load();
        return $this;
    }

    public function getPagerHtml()
    {
            return $this->getChildHtml('pager');
    }

}

Model/Mysql4/Module/Collection.php

class NameSpace_ModuleName_Model_Mysql4_ModuleName_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{

    public function _construct(){

        parent::_construct();
        $this->_init('module/model');

    }


    public function addCustomFilter($customerId)
    {

        $sortByCustomer = 'customer_id = ?';
        $sortByOrder = 'order_id DESC';
        $this->getSelect()->where($sortByCustomer, $customerId)->order(new Zend_Db_Expr($sortByOrder));
        return $this;
    }
}

Model/Mysql4/Modulename.php

class NameSpace_ModuleName_Model_Mysql4_ModuleName extends Mage_Core_Model_Mysql4_Abstract
{

    public function _construct(){    

        // Note that the id refers to the key field in your database table.
        $this->_init('module/model','id');
    }

}

My frontend .phtml file, I am using below code to call my custom collection

<?php
$customerId = 1;
$customerCollection =  $this->getCollection()->addCustomFilter($customerId);

?>


<?php if ($customerCollection->getSize()): ?>
    <div>
                <?php foreach ($customerCollection as $collection): ?>
                    <tr class="first odd">
                        <td style="text-align: center;"><?php echo $collection->someData(); ?>
                    </tr>
                <?php endforeach; ?>
    </div>
<?php endif; ?>

As far as I know, this will call the collection from custom DB table. The issue here is I have already specified the filter in my collection file, but it still not showing filtered results.

Or do I need to add my filter function in Model/Mysql4/Modulename.php file ?

It also throws an exception of Unrecognized method 'getSize()

Please Guide.

Thanks

Answer 1

This line is your problem

$this->getCollection()->load();

You are loading the collection and later you apply a filter in the template.
Once you load the collection all filters and sort orders applied have no effect.
So you should apply the filter before calling load or before iterating through the collection.
In this case you should apply the filter in the block class.

As a general case, the templates should be used only to present data, so all data manipulation should be done in the block classes.

[EDIT]
Make your construct method look like this:

public function __construct()
{
    parent::__construct();
    $collection = Mage::getModel('module/model')->getCollection();
    $customerId = 1;
    $collection->addCustomFilter($customerId);
    $this->setCollection($collection);
}

Answer 2

in Model/Mysql4/Modulename.php

Mage_Core_Model_Mysql4_Collection_Abstract 

Should be:

Mage_Core_Model_Mysql4_Abstract

How to create database module:http://www.amitbera.com/create-an-magento-extension-with-custom-database-table/

Try This

$Collection=Mage::getModel('module/model')->getCollection()->addFieldToFilter('customer_id',$customerId)->addFieldToSelect('*')->setOrder('order_id',DESC);

$collection give array of Record of customer Id

for get one item try this

$Collection->getFirstItem();

EDIT Code for

public function addCustomFilter($customerId =null){
        $this->getSelect()->where("customer_id =?",$customerId) ->order('order_id ' . Varien_Db_Select::SQL_DESC);
        reeturn $this;
        }

and used

Mage::getResourceModel('module/model_collection')

instead of

Mage::getModel('module/model')->getCollection()