Magento – Product List Attribute Filter Query

magento-1.9performanceproduct-attribute

I have added one filter in Method _getProductCollection() of the class Mage_Catalog_Block_Product_List as follows. 

protected function _getProductCollection()
{
     ...
    $this->_productCollection = $layer->getProductCollection();
    $this->_productCollection->getSelect()->joinInner(
        array('cpe' => 'catalog_product_entity'),
        'e.entity_id = cpe.entity_id'
    ) 
    ->where("cpe.type_id = 'simple'"); 
    ...
}

The above code is working fine as of Magento Version 1.7. But whenever I write the following code, it gives

Column not found: 1054 Unknown column 'e.type_id' in 'where clause'

error.

The code (which is not working).

protected function _getProductCollection()
{
     ...
    $this->_productCollection = $layer->getProductCollection();
    $this->_productCollection
        ->addAttributeToSelect('type_id')
        ->addAttributeToFilter('type_id','simple');
    ...
}

Now the Questions.

  1. Will there be any performance impact if I use the first working
    code?
  2. is there any other way to get around to have a proper product
    filter?

UPDATE:

Whenever I apply the following code and use rwd theme, I am not getting any error. But whenever I user default theme, I get the below error,

Code

protected function _getProductCollection()
{
     ...
    $this->_productCollection = $layer->getProductCollection();
    $this->_productCollection
         ->addAttributeToSelect('type_id')
         ->addAttributeToFilter('type_id','simple');
    ...
}

Error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.type_id' in
'where clause', query was: SELECT FLOOR((ROUND((e.min_price) * 1, 2))
/ 10) + 1 AS range, COUNT(*) AS count FROM
catalog_product_index_price AS e 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 = '3' WHERE
(e.type_id IN('simple')) AND ( e.website_id = '1' ) AND (
e.customer_group_id = 0) AND (e.min_price IS NOT NULL) GROUP BY
FLOOR((ROUND((e.min_price) * 1, 2)) / 10) + 1 ORDER BY
FLOOR((ROUND((e.min_price) * 1, 2)) / 10) + 1 ASC

Best Answer

I think that:

$collection->addAttributeToFilter('type_id', array('eq' => 'simple');

Should works, you don't need to add type_id to select since is a catalog_product_entity column and it's retrieved by default. I always suggest to log the final query in order to get a best idea of what is happen:

Mage.:log($collection->getSelectSql(true));

BTW: The first code block has no sense at all since you are joining the main table (catalog_product_entity) to itself.

Related Topic