Fix SQLSTATE[42S22] Column Not Found Error in Magento

collection;layered-navigationproduct-attributeproduct-collection

I am trying to modify a customized Ajax Layered navigation to return products on "AND" base instead of "OR" (return only the products having all selected values for filter attributes.) for this i am using the following code;

protected function applyMultipleValuesFilter($ids)
{
    $attribute  = $this->getAttributeModel();
    $table = Mage::getSingleton('core/resource')
          ->getTableName('catalogindex/eav'); 
    $alias = 'attr_index_'.$attribute->getId();
    $hvngquery = ' ';
    $i= count($ids)-1;
    foreach ($ids as $key => $value) 
    {
        $hvngquery .= '(SUM(`'.$alias.'`.`value`= "'.$value.'") > 0)';
        if ($i>0) 
        {
          $hvngquery .= ' AND ';
         } 
         $i = $i-1;
    }
    $collection = $this->getLayer()->getProductCollection();
    $collection->getSelect()->join(
        array($alias => $table),
        $alias.'.entity_id=e.entity_id',
        array()
    )         
    ->where($alias.'.store_id = ?', Mage::app()->getStore()->getId())
    ->where($alias.'.attribute_id = '. $attribute->getId()) 
     ->group('e.entity_id')
      ->having($hvngquery);

    if (count($ids)>1)
     {
       $collection->getSelect()->distinct(true); 
     }
    // echo $collection->getSelect(); exit();
    return $this;
}

This code produces the following sql query, which while running in phpmyadmin works fine but when i run the code, magento returns error :(Subject)

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 = '24' 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 INNER JOIN `catalog_product_index_eav` AS `attr_index_245` ON attr_index_245.entity_id=e.entity_id WHERE (attr_index_245.store_id = '1') AND (attr_index_245.attribute_id = 245) GROUP BY `e`.`entity_id` HAVING ( (SUM(`attr_index_245`.`value`= "238") > 0) AND (SUM(`attr_index_245`.`value`= "235") > 0))

Best Answer

The question you linked (Issue using "having" in Magento collection) is basically the same problem, but the problematic method is not getSelectCountSql() but Mage_Catalog_Model_Resource_Layer_Filter_Attribute::getCount() (and the other filter classes as well)

You'll see that COLUMNS are resetted, but HAVING is not:

// reset columns, order and limitation conditions
$select->reset(Zend_Db_Select::COLUMNS);
$select->reset(Zend_Db_Select::ORDER);
$select->reset(Zend_Db_Select::LIMIT_COUNT);
$select->reset(Zend_Db_Select::LIMIT_OFFSET);

In the same method, the query gets executed, so you will have to override this method to modify the query object and re-add the necessary columns.

Related Topic