Magento - list.phtml filtering product collection not giving correct pagination

Question

I'm trying to filter list.phtml to suit my needs which is only showing products based of an value of the attribute. The original code to load the product collection is:

$_productCollection=$this->getLoadedProductCollection();
$_helper = $this->helper('catalog/output');

To do the filtering I have got the code:

$_productCollection=$this->getLoadedProductCollection();

$cat_id = Mage::getModel('catalog/layer')->getCurrentCategory()->getId();
$_productCollection = Mage::getResourceModel('catalog/product_collection')
   ->addAttributeToFilter('language', array('eq' => array('English')))
   ->addAttributeToSelect('*')
   ->addCategoryFilter(Mage::getModel('catalog/category')->load($cat_id));


$_helper = $this->helper('catalog/output');

This works however the pagination and items total number (generated from toolbar.phtml and pager.phtml are incorrect. For example the original product collection has the correct pagination of 7 pages and 10 products per page.

However when I use the filter shown above the pagination shows the same 7 pages and every filtered book on one page (there are 18 English books, so 7 pages of the 18 books which are duplicated).

Please can someone help me in solving this pagination issue.

Thanks.

The SQL for the collection is as follows:

 SELECT `e`.*, `at_language`.`value` AS `language`, `cat_index`.`position`
 AS `cat_index_position` FROM `catalog_product_entity`
 AS `e` INNER JOIN `catalog_product_entity_varchar`
 AS `at_language` ON (`at_language`.`entity_id` = `e`.`entity_id`) 
 AND (`at_language`.`attribute_id` = '1002') 
 AND (`at_language`.`store_id` = 0) INNER JOIN `catalog_category_product_index`
 AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 
 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='38' 
 AND cat_index.is_parent=1 WHERE (at_language.value = 'English')

Answer 1

My guess is that you get wrong a product count because the product visibility filter is missing. Try adding it like this:

$_productCollection = Mage::getResourceModel('catalog/product_collection')
   ->addAttributeToFilter('language', array('eq' => array('English')))
   ->addAttributeToSelect('*')
   ->addCategoryFilter(Mage::getModel('catalog/category')->load($cat_id))
   ->setVisibility(
       Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds()
   );

addition:

the custom collection you set to the list.phtml will not be the same the system uses in the paginator. The paginator block Mage_Catalog_Block_Product_List_Toolbar will get the original product collection from $this->_getProductCollection() (see here), which does not have your filter.

I'm afraid, a change to the collection in the template file is not enough. You might have to override the Mage_Catalog_Block_Product_List block, specifically, its function _getProductCollection() to implement the necessary filtering.

addition 2

A proposed override for the function Mage_Catalog_Block_Product_List::_getProductCollection:

protected function _getProductCollection()
{ 
    $collection = parent::_getProductCollection();
    $collection->addAttributeToFilter('language', array('eq' => array('English')));
    $this->_productCollection = $collection;

    return $this->_productCollection;
}