Magento 1.8 Collection Filtering – Filter on Product Listing Using addAttributeToFilter

collection-filteringmagento-1.8

I've been asked to help upgrading a Magento shop from version 1.5.1 to Magento 1.8.1
I'm not a Magento developer, but have much experience with PHP.

The previous developer added a way to filter products based on a custom attributes, width. There are 2 two input fields, width-from and width-to, and when the user input something in them and presses enter, it reloads the product listing page and adds them as GET paramters (http://site.com../?width_from=...).

Since there is no documentation of the solution, I've tried looking around to see where it is implemented. As far as I can tell, modifications have been made to app/code/local/Mage/Catalog/Model/Layer.php, where the function getProductCollection()have been altered:

public function getProductCollection()
{
    if (isset($this->_productCollections[$this->getCurrentCategory()->getId()])) {
            $collection = $this->_productCollections[$this->getCurrentCategory()->getId()];
    } else {
        $collection = $this->getCurrentCategory()->getProductCollection();
        $this->prepareProductCollection($collection);
            $this->_productCollections[$this->getCurrentCategory()->getId()] = $collection;
    }
        if(!empty($_GET['width-from'])) {
            $collection->addAttributeToFilter('width', array('gteq' => (int) $_GET['width-from']));
        }
        if(!empty($_GET['width-to'])) {
            $collection->addAttributeToFilter('width', array('lteq' => (int) $_GET['width-to']));
        }

            //$collection->printlogquery(true);

    return $collection;
}

So, as you can see the previous developer used addAttributetoFilter to add the filter. However, I'm having problems moving this code to Magento 1.8.1. I've tried to add the following to getProductCollection in app/code/core/Mage/Catalog/Model/Layer.php:

public function getProductCollection()
{
    if (isset($this->_productCollections[$this->getCurrentCategory()->getId()])) {
        $collection = $this->_productCollections[$this->getCurrentCategory()->getId()];
    } else {
        $collection = $this->getCurrentCategory()->getProductCollection();
        $this->prepareProductCollection($collection);
        $this->_productCollections[$this->getCurrentCategory()->getId()] = $collection;
    }

    $collection->clear();

        if(!empty($_GET['width-from'])) {
            $collection->addAttributeToFilter('width', array('gteq' => (int) $_GET['width-from']));
        }
        if(!empty($_GET['width-to'])) {
            $collection->addAttributeToFilter('width', array('lteq' => (int) $_GET['width-to']));
        }

    $collection->load();    

           // $collection->printlogquery(true);

    return $collection;
}

Basically, I've added the calls to addAtributetoFilter, as well as called the clear()and load() method (just to check, doesn't seem to make a difference). However, when I input something in the filters it causes the following error:

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

Trace:
#0 /var/www/shop-reza.dk/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/shop-reza.dk/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/shop-reza.dk/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/shop-reza.dk/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT FLOOR((R...', Array)
#4 /var/www/shop-reza.dk/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT FLOOR((R...', Array)
#5 /var/www/shop-reza.dk/public_html/lib/Zend/Db/Adapter/Abstract.php(808): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(274): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 /var/www/shop-reza.dk/public_html/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), 1000)
#8 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(115): Mage_Catalog_Model_Layer_Filter_Price->getRangeItemCounts(1000)
#9 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(314): Mage_Catalog_Model_Layer_Filter_Price->getPriceRange()
#10 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(151): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
#11 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(120): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
#12 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#13 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(132): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
#14 /var/www/shop-reza.dk/public_html/app/design/frontend/default/theme213/template/catalog/layer/view.phtml(46): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
#15 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/var/www/shop-r...')
#16 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#17 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#18 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#19 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#20 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#21 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#22 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('left', true)
#23 /var/www/shop-reza.dk/public_html/app/design/frontend/default/theme213/template/page/2columns-left.phtml(57): Mage_Core_Block_Abstract->getChildHtml('left')
#24 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/var/www/shop-r...')
#25 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#26 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#27 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#28 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#29 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#30 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Catalog/controllers/CategoryController.php(161): Mage_Core_Controller_Varien_Action->renderLayout()
#31 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Catalog_CategoryController->viewAction()
#32 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#33 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#34 /var/www/shop-reza.dk/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#35 /var/www/shop-reza.dk/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#36 /var/www/shop-reza.dk/public_html/index.php(87): Mage::run('', 'store')
#37 {main}

Actually the filtering works. If I in the file app/design/frontend/default/theme213/template/catalog/layer/view.phtml as the first thing writes <?php die(); ?>, the execution stops before the problematic code. This means that the product listing is displayed (correct with the filters applied), but for example the layered navigation block is removed.

I suspect that the way this filtering is done is horrible wrong with respect to Magento development. Thus, if any of you have a better/preferred way to apply filters on the product listing page (based on some GET parameters), let me know!

Best Answer

Finally got it working, although not in the optimal way.

By tracing the SQL query actually executed by Magento, I noticed that the table it looked in was in catalog_product_flat_1. So, in Configuration → Catalog → Product I disabled Use Flat Catalog Product and then made a reindex.

Related Solutions

Magento – Magento 1.8 reindex all

The catalog_product_index_eav table has a foreign key contraint named

FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID

Looking at this table's definition

CREATE TABLE `catalog_product_index_eav` (
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID` (`entity_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
  KEY `IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`),
  CONSTRAINT `FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

we can see the foreign key definition is

CONSTRAINT `FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` 
FOREIGN KEY (`entity_id`) 
REFERENCES `catalog_product_entity` (`entity_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

This means for every entity_id row in catalog_product_index_eav, there needs to be an identical, corresponding entity_id value in catalog_product_entity.

The root of your problem is for some reason (either a rouge extension, errors caused by randomly typing in SQL from the internet, or performing data updates with disabled indexes), Magento's indexing attempts to update data in catalog_product_index_eav that violates this rule. The next step is identifying what Magento's doing so you can fix the data.

If we look at your call stack, this looks like a good place to start debugging

#10 app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Abstract.php(54): 
Mage_Index_Model_Resource_Abstract->syncData()

Jumping to that source file, we see the following bit of code

public function syncData()
{
    $this->beginTransaction();
    try {
        /**
         * Can't use truncate because of transaction
         */
        $this->_getWriteAdapter()->delete($this->getMainTable());
        $this->insertFromTable($this->getIdxTable(), $this->getMainTable(), false);
        $this->commit();
    } catch (Exception $e) {
        $this->rollBack();
        throw $e;
    }
    return $this;
}

As part of its indexing process, Magento tries to sync data from an "index table" (getIdxTable), to a "source" (getMainTable) table.

public function insertFromTable($sourceTable, $destTable, $readToIndex = true)
{
    //...
}

For this particular index, the index table is catalog_product_index_eav_idx, and the source table is catalog_product_index_eav.

Note: Be careful with your terminology around here, things are confusingly named. The "Source" table is the table we're copying to. (I believe it's called the source table because it's the "source" a normal Magento system will query from when it needs information)

So, Magento is trying to sync a row from catalog_product_index_eav_idx to the table catalog_product_index_eav. However, this causes the previously mentioned foreign key error. This leads us to two possible conclusions

The catalog_product_index_eav_idx has entity_id rows that do not exist in catalog_product_entity.
The catalog_product_index_eav table has (through previous manipulation with index checks turned off) entity_id rows that do not exist in catalog_product_entity.

So, your mission here is to figure out which entity_id rows in catalog_product_index_eav and catalog_product_index_eav_idx don't exist in catalog_product_entity, and manually delete said rows (from catalog_product_index_eav and catalog_product_index_eav_idx).

If it were me, and my catalog_product_entity table wasn't too large, I'd start with the following queries (these are untested, as I don't have any Magento tables with the above invalid data states)

SELECT * 
FROM catalog_product_index_eav_idx 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

SELECT * FROM catalog_product_index_eav 
WHERE NOT (entity_id IN (SELECT entity_id from catalog_product_entity));

Good luck!

Magento – Get Product Collection with In-Stock Products Only

To answer the question. You would use this (not very different than your version):

$productCollection = Mage::getResourceModel('catalog/product_collection')
                        ->addAttributeToSelect(array('name', 'image', 'price'))
                        ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED));

Mage::getSingleton('cataloginventory/stock')
    ->addInStockFilterToCollection($productCollection);

$this->_productCollection = $productCollection;

But I don't know exactly what causes your error, because it seems related to the fact that the getSubmitUrl/getAddToCartUrl product param is boolean false, and not to the collection per se.

Best Answer

Related Solutions

Magento – Magento 1.8 reindex all

Magento – Get Product Collection with In-Stock Products Only

Related Topic