3

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))
1
  • if found these two question but couldn't get help from This and This
    – Haris
    Sep 14, 2015 at 13:02

1 Answer 1

1

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.

1
  • 1
    Thanks for your answer, my problem was not exactly solved by getCount() instead i modified _getBaseCollectionSql() to achieve the desired output
    – Haris
    Sep 15, 2015 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.