/** * 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; }