/** * Apply price rule price to price index table * * @param Varien_Db_Select $select * @param array|string $indexTable * @param string $entityId * @param string $customerGroupId * @param string $websiteId * @param array $updateFields the array of fields for compare with rule price and update * @param string $websiteDate * @return Mage_CatalogRule_Model_Resource_Rule_Product_Price */ public function applyPriceRuleToIndexTable(Varien_Db_Select $select, $indexTable, $entityId, $customerGroupId, $websiteId, $updateFields, $websiteDate) { if (empty($updateFields)) { return $this; } if (is_array($indexTable)) { foreach ($indexTable as $k => $v) { if (is_string($k)) { $indexAlias = $k; } else { $indexAlias = $v; } break; } } else { $indexAlias = $indexTable; } $select->join(array('rp' => $this->getMainTable()), "rp.rule_date = {$websiteDate}", array())->where("rp.product_id = {$entityId} AND rp.website_id = {$websiteId} AND rp.customer_group_id = {$customerGroupId}"); foreach ($updateFields as $priceField) { $priceCond = $this->_getWriteAdapter()->quoteIdentifier(array($indexAlias, $priceField)); $priceExpr = $this->_getWriteAdapter()->getCheckSql("rp.rule_price < {$priceCond}", 'rp.rule_price', $priceCond); $select->columns(array($priceField => $priceExpr)); } $query = $select->crossUpdateFromSelect($indexTable); $this->_getWriteAdapter()->query($query); return $this; }
/** * @param array|string $attributes * @param Varien_Db_Select $select * @param string $mainCorrelation * @param string $mainIdColumn * @return $this */ public function includeProductAttribute($attributes, $select, $mainCorrelation = 'main_table', $mainIdColumn = null, $joinTableAlias = null, $resourceName = null) { $attributes = $this->_loadAttributes($attributes); $mainIdColumn = $mainIdColumn ?: 'entity_id'; $joinTableAlias = $joinTableAlias ?: 'product_%s_table'; foreach ($attributes as $attributeCode) { /** @var Mage_Eav_Model_Entity_Attribute $attribute */ if (is_string($attributeCode)) { $attribute = $this->_getCachedAttribute($attributeCode); } else { $attribute = $attributeCode; } $attributeCode = $attribute->getAttributeCode(); if ($attribute->getId()) { if ($attribute->getBackendType() == 'static') { $select->columns($attribute->getAttributeCode()); } else { $joinTable = sprintf($joinTableAlias, $attributeCode); $select->joinLeft(array($joinTable => $attribute->getBackendTable()), sprintf('`%s`.%s = `%s`.entity_id AND `%s`.attribute_id = %s', $mainCorrelation, $mainIdColumn, $joinTable, $joinTable, $attribute->getAttributeId()), array($attributeCode => new Zend_Db_Expr(sprintf('`%s`.value', $joinTable)))); } } } return $this; }
/** * Adds Columns prepared for union * * @param Varien_Db_Select $select * @param string $table * @param string $type * @return Varien_Db_Select */ protected function _addLoadAttributesSelectFields($select, $table, $type) { $select->columns(Mage::getResourceHelper('catalog')->attributeSelectFields('attr_table', $type)); return $select; }
/** * Join information for usin full text search * * @param Varien_Db_Select $select * @return Varien_Db_Select $select */ public function chooseFulltext($table, $alias, $select) { $field = new Zend_Db_Expr('MATCH (' . $alias . '.data_index) AGAINST (:query IN BOOLEAN MODE)'); $select->columns(array('relevance' => $field)); return $field; }
/** * Adds order by random to select object * Possible using integer field for optimization * * @param Varien_Db_Select $select * @param string $field * @return Varien_Db_Adapter_Pdo_Mysql */ public function orderRand(Varien_Db_Select $select, $field = null) { if ($field !== null) { $expression = new Zend_Db_Expr(sprintf('RAND() * %s', $this->quoteIdentifier($field))); $select->columns(array('mage_rand' => $expression)); $spec = new Zend_Db_Expr('mage_rand'); } else { $spec = new Zend_Db_Expr('RAND()'); } $select->order($spec); return $this; }
/** * @param Varien_Db_Select $select * @param string $table * @param string $type * @return Varien_Db_Select */ protected function _addLoadAttributesSelectValues($select, $table, $type) { $helper = Mage::getResourceHelper('eav'); $select->columns(array('value' => $helper->prepareEavAttributeValue($table . '.value', $type))); return $select; }
/** * @param Varien_Db_Select $select * @param string $table * @param string $type * @return Varien_Db_Select */ protected function _addLoadAttributesSelectValues($select, $table, $type) { $storeId = $this->getStoreId(); if ($storeId) { $helper = Mage::getResourceHelper('eav'); $adapter = $this->getConnection(); $valueExpr = $adapter->getCheckSql( 't_s.value_id IS NULL', $helper->prepareEavAttributeValue('t_d.value', $type), $helper->prepareEavAttributeValue('t_s.value', $type) ); $select->columns(array( 'default_value' => $helper->prepareEavAttributeValue('t_d.value', $type), 'store_value' => $helper->prepareEavAttributeValue('t_s.value', $type), 'value' => $valueExpr )); } else { $select = parent::_addLoadAttributesSelectValues($select, $table, $type); } return $select; }
/** * Enter description here ... * @param Varien_Db_Select $select * @param string $column */ public function selectColumn($select, $column) { list($expr, $alias) = explode(' AS ', $column); foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnInfo) { if ($columnInfo[2] == $alias) { return $this; } } $select->columns($column); return $this; }
/** * Add prepared column group_concat expression * * @param Varien_Db_Select $select * @param string $fieldAlias Field alias which will be added with column group_concat expression * @param string $fields * @param string $groupConcatDelimiter * @param string $fieldsDelimiter * @param string $additionalWhere * @return Varien_Db_Select */ public function addGroupConcatColumn($select, $fieldAlias, $fields, $groupConcatDelimiter = ',', $fieldsDelimiter = '', $additionalWhere = '') { if (is_array($fields)) { $fieldExpr = $this->_getReadAdapter()->getConcatSql($fields, $fieldsDelimiter); } else { $fieldExpr = $fields; } if ($additionalWhere) { $fieldExpr = $this->_getReadAdapter()->getCheckSql($additionalWhere, $fieldExpr, "''"); } $separator = ''; if ($groupConcatDelimiter) { $separator = sprintf(" SEPARATOR '%s'", $groupConcatDelimiter); } $select->columns(array($fieldAlias => new Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator)))); return $select; }
public function addLinkUrlToFlatCatalogSelect(Varien_Db_Select $select) { $select->columns(array('url' => VinaiKopp_CategoryLink_Helper_Data::ATTR_CODE_LINK)); }
/** * FastIndexer * Keep method private because of the last two args which can be used for SQL injections * * @param Varien_Db_Select $select * @param string $attributeCode * @param int $storeId * @param string $sqlCompare * @param string|int $attributeValue * @param bool $addAttributeToColumn */ private function _addProductAttributeToSelect(Varien_Db_Select $select, $attributeCode, $storeId, $sqlCompare = null, $attributeValue = null, $addAttributeToColumn = true) { $adapter = $this->_getReadAdapter(); /** @var $adapter SchumacherFM_FastIndexer_Model_Db_Adapter_Pdo_Mysql */ if (!isset($this->_productAttributes[$attributeCode])) { $attribute = $this->getProductModel()->getResource()->getAttribute($attributeCode); $this->_productAttributes[$attributeCode] = array('entity_type_id' => $attribute->getEntityTypeId(), 'attribute_id' => $attribute->getId(), 'table' => $attribute->getBackend()->getTable(), 'is_global' => $attribute->getIsGlobal()); unset($attribute); } $tableAlias = 'tj' . $attributeCode; $tAttrId = (int) $this->_productAttributes[$attributeCode]['attribute_id']; $tTypeId = (int) $this->_productAttributes[$attributeCode]['entity_type_id']; if (1 === (int) $this->_productAttributes[$attributeCode]['is_global'] || $storeId == 0) { $joinColumns = array($tableAlias . '.entity_type_id = ' . $tTypeId, $tableAlias . '.store_id = 0', $tableAlias . '.attribute_id = ' . $tAttrId, 'e.entity_id = ' . $tableAlias . '.entity_id'); if (null !== $sqlCompare && null !== $attributeValue) { $joinColumns[] = $tableAlias . '.value ' . $sqlCompare . ' ' . $attributeValue; } $select->join(array($tableAlias => $this->_productAttributes[$attributeCode]['table']), implode(' AND ', $joinColumns), array()); if (true === $addAttributeToColumn) { $select->columns(array($attributeCode => $tableAlias . '.value')); } } else { $t1 = 't1' . $tableAlias; $t2 = 't2' . $tableAlias; $valueExpr = $adapter->getCheckSql('IFNULL(' . $t2 . '.value_id,0) > 0', $t2 . '.value', $t1 . '.value'); $select->join(array($t1 => $this->_productAttributes[$attributeCode]['table']), 'e.entity_id = ' . $t1 . '.entity_id AND ' . $t1 . '.store_id = 0 AND ' . $t1 . '.attribute_id = ' . $tAttrId, array())->joinLeft(array($t2 => $this->_productAttributes[$attributeCode]['table']), $t1 . '.entity_id = ' . $t2 . '.entity_id AND ' . $t1 . '.attribute_id = ' . $t2 . '.attribute_id AND ' . $t2 . '.store_id = ' . $storeId, array()); if (true === $addAttributeToColumn) { $select->columns(array($attributeCode => $valueExpr)); } if (null !== $sqlCompare && null !== $attributeValue) { $select->where($valueExpr . ' ' . $sqlCompare . ' ' . $attributeValue); } } }