/** * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $productCollection * @param $categoryCollection * @param bool $inCurrentCategory * @return $this */ public function addCountToCategories($productCollection, $categoryCollection, $inCurrentCategory = false) { Mana_Core_Profiler2::start(__METHOD__); $isAnchor = array(); $isNotAnchor = array(); foreach ($categoryCollection as $category) { if ($category->getIsAnchor()) { $isAnchor[] = $category->getId(); } else { $isNotAnchor[] = $category->getId(); } } $productCounts = array(); if ($isAnchor || $isNotAnchor) { /* @var $select Varien_Db_Select */ $select = $productCollection->getProductCountSelect(); if ($inCurrentCategory) { $from = $select->getPart(Varien_Db_Select::FROM); if (isset($from['cat_index'])) { $categoryId = $this->getLayer()->getCurrentCategory()->getId(); $from['cat_index']['joinCondition'] = preg_replace("/(.*)(`?)cat_index(`?).(`?)category_id(`?)='(\\d+)'(.*)/", "\$1\$2cat_index\$3.\$4category_id\$5='{$categoryId}'\$7", $from['cat_index']['joinCondition']); $select->setPart(Varien_Db_Select::FROM, $from); } } Mage::dispatchEvent('catalog_product_collection_before_add_count_to_categories', array('collection' => $productCollection)); if ($isAnchor) { $anchorStmt = clone $select; $anchorStmt->limit(); //reset limits $anchorStmt->where('count_table.category_id IN (?)', $isAnchor); $sql = $anchorStmt->__toString(); $productCounts += $productCollection->getConnection()->fetchPairs($anchorStmt); $anchorStmt = null; } if ($isNotAnchor) { $notAnchorStmt = clone $select; $notAnchorStmt->limit(); //reset limits $notAnchorStmt->where('count_table.category_id IN (?)', $isNotAnchor); $notAnchorStmt->where('count_table.is_parent = 1'); $productCounts += $productCollection->getConnection()->fetchPairs($notAnchorStmt); $notAnchorStmt = null; } $select = null; $productCollection->unsProductCountSelect(); } foreach ($categoryCollection as $category) { $_count = 0; if (isset($productCounts[$category->getId()])) { $_count = $productCounts[$category->getId()]; } $category->setProductCount($_count); } Mana_Core_Profiler2::stop(); return $this; }
/** * Add price index filter * * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection * * @return Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection */ public function addPriceIndexFilter($collection) { $select = $collection->getSelect(); $fromPart = $select->getPart(Zend_Db_Select::FROM); if (isset($fromPart['price_index'])) { $joinCond = $fromPart['price_index']['joinCondition']; if (strpos($joinCond, 'price_index.store_id') === false) { $helper = $this->getStorePricingHelper(); $connection = $collection->getConnection(); if (!$collection->getFlag('store_id')) { $storeId = $helper->getCurrentStoreId(); } else { $storeId = $collection->getFlag('store_id'); } $storeId = $connection->quote($storeId); if ($storeId) { $joinCond .= " AND (price_index.store_id = {$storeId})"; } else { $joinCond .= " AND (price_index.store_id = 0)"; } $fromPart['price_index']['joinCondition'] = $joinCond; $select->setPart(Zend_Db_Select::FROM, $fromPart); } } return $collection; }
/** * prepare sql for attribute compare * * @param object $attribute - attribute class with data * @param array $joinedAttributes - attribute which was joined into query * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection - product collection * @param string $operator - operator for compare * @param string $value - value for compare * @return string $where - sql query */ public function prepareSqlForAtt($attribute, $joinedAttributes, $collection, $operator, $value) { $nOperator = array('==', '!{}', '{}', '!()', '()'); $mOperator = array('=', 'NOT LIKE', 'LIKE', 'NOT IN', 'IN'); //category operator $cnOperator = array('==', '!=', '<=', '>=', '=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE'); $cmOperator = array('IN', 'NOT IN', 'NOT IN', 'NOT IN', 'IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN'); $operator = str_replace($nOperator, $mOperator, $operator); if ($attribute == 'category_ids') { $operator = str_replace($cnOperator, $cmOperator, $operator); } if (($operator == 'LIKE' || $operator == 'NOT LIKE') && is_array($value)) { if ($operator == 'LIKE') { $operator = 'FIND_IN_SET'; } else { $operator = '!FIND_IN_SET'; } } /* Quote rule value depending operator type */ switch ($operator) { case 'LIKE': case 'NOT LIKE': $value = $collection->getConnection()->quote("%{$value}%"); break; case 'IN': case 'NOT IN': if (!is_array($value)) { $value = array_filter(array_map('trim', explode(',', $value))); } $value = '(' . $collection->getConnection()->quote($value) . ')'; break; case 'FIND_IN_SET': case '!FIND_IN_SET': $_resultSql = '(' . $collection->getConnection()->quote($value[0]) . ', att_table_' . $attribute . '.' . 'value)'; unset($value[0]); foreach ($value as $_conditionValue) { $_resultSql .= ' OR ' . $operator . '(' . $collection->getConnection()->quote($_conditionValue) . ', att_table_' . $attribute . '.' . 'value)'; } $value = $_resultSql; break; default: if (is_array($value)) { $value = implode(',', $value); } $value = $collection->getConnection()->quote($value); } if (!in_array($attribute, $this->_joinedAttributes)) { array_push($this->_joinedAttributes, $attribute); $att = Mage::getModel('catalog/product')->getResource()->getAttribute($attribute); if (!$att && $attribute != 'category_ids') { return null; } switch ($attribute) { case 'sku': $collection->getSelect()->join(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('sku')); break; case 'category_ids': $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $collection->getTable('catalog/category_product')), 'att_table_' . $attribute . '.product_id = e.entity_id', array('category_id')); break; case 'attribute_set_id': $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('attribute_set_id')); break; case 'price': $collection->addPriceData(); break; default: $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id AND att_table_' . $attribute . '.attribute_id = ' . $att->getId(), array('value')); } } switch ($attribute) { case 'sku': $where = '(att_table_' . $attribute . '.' . 'sku' . ' ' . $operator . ' ' . $value . ')'; break; case 'category_ids': $where = '(IFNULL(att_table_' . $attribute . '.' . 'category_id,\'\')' . ' ' . $operator . ' ' . $value . ' AND e.entity_id ' . $operator . '(SELECT `product_id` FROM `' . $collection->getTable('catalog/category_product') . '` WHERE `category_id` IN' . $value . ')' . ' )'; break; case 'price': $where = '(price_index.final_price ' . $operator . ' ' . $value . ')' . ' AND (price_index.min_price ' . $operator . ' ' . $value . ')'; break; case 'attribute_set_id': $where = '(IFNULL(att_table_' . $attribute . '.' . 'attribute_set_id,\'\')' . ' ' . $operator . ' ' . $value . ')'; break; default: $where = '(IFNULL(att_table_' . $attribute . '.' . 'value,\'\')' . ' ' . $operator . ' ' . $value . ')'; if ($operator == 'FIND_IN_SET' || $operator == '!FIND_IN_SET') { $where = '(' . $operator . ' ' . $value . ')'; } } return $where; }
/** * prepare sql for attribute compare * * @param object $attribute - attribute class with data * @param array $joinedAttributes - attribute which was joined into query * @param Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection $collection - product collection * @param string $operator - operator for compare * @param string $value - value for compare * @return string $where - sql query */ public function prepareSqlForAtt($attribute, $joinedAttributes, $collection, $operator, $value) { $nOperator = array('==', '!{}', '{}', '!()', '()'); $mOperator = array('=', 'NOT LIKE', 'LIKE', 'NOT IN', 'IN'); //category operator //$cnOperator = array('==', '!=', '=', '>=', '<=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE'); $cnOperator = array('==', '!=', '<=', '>=', '=', '>', '<', '!{}', '{}', '!()', '()', 'NOT LIKE', 'LIKE'); //$cmOperator = array('=','!=','!=','!=','!=','!=','NOT IN','IN','NOT IN','IN'); //$cmOperator = array('IN', 'NOT IN', 'IN', 'NOT IN', 'NOT IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN'); $cmOperator = array('IN', 'NOT IN', 'NOT IN', 'NOT IN', 'IN', 'NOT IN', 'NO IN', 'NOT IN', 'IN', 'NOT IN', 'IN', 'NOT IN', 'IN'); $operator = $attribute == 'category_ids' ? str_replace($cnOperator, $cmOperator, $operator) : str_replace($nOperator, $mOperator, $operator); /* Quote rule value depending operator type */ switch ($operator) { case 'LIKE': case 'NOT LIKE': $value = $collection->getConnection()->quote("%{$value}%"); break; case 'IN': case 'NOT IN': $value = '(' . $collection->getConnection()->quote(explode(',', $value)) . ')'; break; default: $value = $collection->getConnection()->quote($value); } /* */ if (!in_array($attribute, $this->_joinedAttributes)) { array_push($this->_joinedAttributes, $attribute); //$this->_joinedAttributes = $joinedAttributes; $att = Mage::getModel('catalog/product')->getResource()->getAttribute($attribute); if (!$att && $attribute != 'category_ids') { return null; } switch ($attribute) { case 'sku': $collection->getSelect()->join(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('sku')); break; case 'category_ids': $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $collection->getTable('catalog/category_product')), 'att_table_' . $attribute . '.product_id = e.entity_id', array('category_id')); break; case 'attribute_set_id': $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id', array('attribute_set_id')); break; case 'price': $collection->addPriceData(); break; default: $collection->getSelect()->joinLeft(array('att_table_' . $attribute => $att->getBackend()->getTable()), 'att_table_' . $attribute . '.entity_id = e.entity_id AND att_table_' . $attribute . '.attribute_id = ' . $att->getId(), array('value')); } } switch ($attribute) { case 'sku': $where = '(att_table_' . $attribute . '.' . 'sku' . ' ' . $operator . ' ' . $value . ')'; break; case 'category_ids': $where = '(IFNULL(att_table_' . $attribute . '.' . 'category_id,\'\')' . ' ' . $operator . ' ' . $value . ' AND e.entity_id ' . $operator . '(SELECT `product_id` FROM `' . $collection->getTable('catalog/category_product') . '` WHERE `category_id` IN' . $value . ')' . ' )'; break; case 'price': $where = $collection->getConnection()->quoteInto('(price_index.final_price ' . $operator . '?)', $value); break; case 'attribute_set_id': $where = '(IFNULL(att_table_' . $attribute . '.' . 'attribute_set_id,\'\')' . ' ' . $operator . ' ' . $value . ')'; break; default: $where = '(IFNULL(att_table_' . $attribute . '.' . 'value,\'\')' . ' ' . $operator . ' ' . $value . ')'; } return $where; }