Can not load and filter using type_id on a product collection

Question

I want to overwrite the function prepareProductCollection($collection) of the catalog/layer model.

Because, I only want to display simple products, so i want to do :

 public function prepareProductCollection($collection)
    {
        $collection
           ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
           ->addMinimalPrice()
           ->addFinalPrice()
           ->addTaxPercents()
           ->addUrlRewrite($this->getCurrentCategory()->getId());

        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

        $collection
         ->addAttributeToSelect('type_id')
         ->addAttributeToFilter('type_id','simple');
       echo $collection->getSelect()->__toString();

    }

But when i do that, i have this error :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.type_id' in 'where clause'

Trace:
#0 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT FLOOR((R...', Array)
#4 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT FLOOR((R...', Array)
#5 /Users/Ditchou/Documents/ProjetsWeb/lolote/lib/Zend/Db/Adapter/Abstract.php(808): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(274): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(158): Mage_Catalog_Model_Resource_Layer_Filter_Price->getCount(Object(Mage_Catalog_Model_Layer_Filter_Price), 10)
#8 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(115): Mage_Catalog_Model_Layer_Filter_Price->getRangeItemCounts(10)
#9 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(314): Mage_Catalog_Model_Layer_Filter_Price->getPriceRange()
#10 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(151): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
#11 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(120): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
#12 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#13 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(132): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
#14 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/design/frontend/decostore/default/template/catalog/layer/view.phtml(49): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
#15 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(241): include('/Users/Ditchou/...')
#16 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/decost...')
#17 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#18 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Template->_toHtml()
#19 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#20 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
#21 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#22 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('left', true)
#23 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/design/frontend/decostore/default/template/page/2columns-left.phtml(19): Mage_Core_Block_Abstract->getChildHtml('left')
#24 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(241): include('/Users/Ditchou/...')
#25 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/decost...')
#26 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#27 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Template->_toHtml()
#28 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#29 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#30 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/controllers/CategoryController.php(159): Mage_Core_Controller_Varien_Action->renderLayout()
#31 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Catalog_CategoryController->viewAction()
#32 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#33 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#34 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#35 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#36 /Users/Ditchou/Documents/ProjetsWeb/lolote/index.php(87): Mage::run('', 'store')
#37 {main}

The error seems to occurs here : #6 /Users/Ditchou/Documents/ProjetsWeb/lolote/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(274): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))

I do not understand because when I simply do

$collectionSimple = Mage::getResourceModel('catalog/product_collection')
     ->addAttributeToFilter('type_id', array('eq' => 'simple'));

The echo of the sql request gives me :

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` 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' INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 WHERE (`e`.`type_id` = 'simple')

it works.

Why the collection of the layer model is different?

Answer 1

Are you sure that's the error you're getting? In Magento 1.7.x, when I replace the prepareProductCollection method body with the one provided above, I get this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.min_price' in 'where clause'

That's because my prepareProductCollection didn't set the same properties as the original. For starters, make sure yours does. If you're doing this with a config based rewrite, then call

parent::prepareProductCollection($collection);

at the start of your method. If you're using a local or community code pool override, then you'll need to copy/paste the code from the core file. In 1.7.1, that will look like this

    $collection
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addUrlRewrite($this->getCurrentCategory()->getId());

    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);

    $collection
        ->addAttributeToSelect('type_id')
        ->addAttributeToFilter('type_id','simple');

If that doesn't help you, trace your callstack — if your error really is a 1054 Unknown column 'e.type_id' in 'where clause' then my guess is you've got some custom code running somewhere that copies the collection's filters to a different collection.

Also, you can get a collection's primary SQL query by using

echo $collection->getSelect()->__toString();

That should give you enough debugging information to get going.

Update: OK, based on the new information above, this appears to kick in only where there's a price filter, and/or when the indexes are in a particular state.

Here's your problem. Take a look at this call-stack line

#7 app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(158):    Mage_Catalog_Model_Resource_Layer_Filter_Price->getCount(Object(Mage_Catalog_Model_Layer_Filter_Price), 10)

If you jump to the getCount method, you'll see a call to _getSelect

#File: app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php 
public function getCount($filter, $range)
{
    $select = $this->_getSelect($filter);

If you look at the definition of _getSelect,

#partial method reproduction
#File: app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php
protected function _getSelect($filter)
{    
    $collection = $filter->getLayer()->getProductCollection();
    $collection->addPriceData($filter->getCustomerGroupId(), $filter->getWebsiteId());
    if (!is_null($collection->getCatalogPreparedSelect())) {
        $select = clone $collection->getCatalogPreparedSelect();
    } else {
        $select = clone $collection->getSelect();
    }

you'll see Magento is cloning the select from the collection, and then modifying it to select from the index table

    // processing FROM part
    $priceIndexJoinPart = $fromPart[Mage_Catalog_Model_Resource_Product_Collection::INDEX_TABLE_ALIAS];
    $priceIndexJoinConditions = explode('AND', $priceIndexJoinPart['joinCondition']);
    $priceIndexJoinPart['joinType'] = Zend_Db_Select::FROM;
    $priceIndexJoinPart['joinCondition'] = null;
    $fromPart[Mage_Catalog_Model_Resource_Product_Collection::MAIN_TABLE_ALIAS] = $priceIndexJoinPart;
    unset($fromPart[Mage_Catalog_Model_Resource_Product_Collection::INDEX_TABLE_ALIAS]);
    $select->setPart(Zend_Db_Select::FROM, $fromPart);
    foreach ($fromPart as $key => $fromJoinItem) {
        $fromPart[$key]['joinCondition'] = $this->_replaceTableAlias($fromJoinItem['joinCondition']);
    }
    $select->setPart(Zend_Db_Select::FROM, $fromPart);

Since the index table doesn't have a type_id column, the above stock Magento code is incompatible with your change. If I was going to proceed on this, I'd

1. Move to a rewrite based approach

2. Create a rewrite that adds your type_id filter in place of your override

3. Create a second rewrite on the _getSelect method above that checks the select for a type_id filter, and if it finds it, removes it

Alternately, you could try finding a different method to rewrite your collection object that's closer to where it's used.

Good luck!

Answer 2

I had issues with extending the _getSelect function not returning the correct set for the price filters in the sidebar when I removed the fields that were causing the error from the select statement.

So instead to get around the issue with the fields not existing I used a field that does exist, entity_id. I extended Mage_Catalog_Model_Category, changing the function getProductCollection:

$collection = Mage::getResourceModel('catalog/product_collection')
    ->setStoreId($this->getStoreId())
    ->addAttributeToFilter('type_id', array('eq' => 'simple'));
// Get the product ids of the filtered collection
$entityIds = $collection->getAllIds();

$collection = Mage::getResourceModel('catalog/product_collection')
    ->setStoreId($this->getStoreId())
    ->addCategoryFilter($this)
    // Apply the product ids as a filter
    ->addIdFilter($entityIds,false);

return $collection;

Answer 3

I just had the same issue 2-3 weeks ago when I attempted to add layer navigation to custom collections (not collection that depend on a categroy nor search collections). I had the same problem that you encountered which originates from the price filter processing used in layered navigation. Like Alan suggested, in Magento 1.7 they did an optimization on the select sql that is used for the price filtering options. They remove the FROM sql part (which is product entity - which contains type_id attribute) and make the price_index related table the main one - the one that is used in the FROM part of the query. So basically the table from which the data is selected is not the product entity table anymore (you lose all products base attributes) the main table becomes the product index. This is an core update that the Magento team added starting 1.7 versions.

I also have a working fix for this, I will share it with you tomorrow, I don't have access to the code now.