/** * Check structure of sql query * * @param Varien_Db_Select $select * @return array */ public function fetchAllCallback(Varien_Db_Select $select) { $whereParts = $select->getPart(Varien_Db_Select::WHERE); $this->assertCount(2, $whereParts); $this->assertContains("rule_name IS NOT NULL", $whereParts[0]); $this->assertContains("rule_name <> ''", $whereParts[1]); $orderParts = $select->getPart(Varien_Db_Select::ORDER); $this->assertCount(1, $orderParts); $expectedOrderParts = array('rule_name', 'ASC'); $this->assertEquals($expectedOrderParts, $orderParts[0]); return $this->_rules; }
/** * Remove price records from where query * * @param Varien_Db_Select $select * @param string $priceExpression * @return Varien_Db_Select */ public function _removePriceFromSelect($select, $priceExpression) { $oldWhere = $select->getPart(Varien_Db_Select::WHERE); $newWhere = array(); foreach ($oldWhere as $cond) { if (false === strpos($cond, $priceExpression)) { $newWhere[] = $cond; } } if ($newWhere && substr($newWhere[0], 0, 3) == 'AND') { $newWhere[0] = substr($newWhere[0], 3); } $select->setPart(Varien_Db_Select::WHERE, $newWhere); return $select; }
/** * @param Varien_Db_Select $select * @param string $attributeCode * @return $this */ public function joinAttribute($select, $attributeCode) { /* @var $core Mana_Core_Helper_Data */ $core = Mage::helper(strtolower('Mana_Core')); /* @var $attribute Mage_Catalog_Model_Resource_Eav_Attribute */ $attribute = $core->collectionFind($this->getAttributes(), 'attribute_code', $attributeCode); /* @var $db Varien_Db_Adapter_Pdo_Mysql */ $db = $select->getAdapter(); $alias = 'meav_' . $attributeCode; $storeAlias = 's' . $alias; $from = $select->getPart(Varien_Db_Select::FROM); if (!isset($from[$alias])) { $select->joinLeft(array($alias => $attribute->getBackendTable()), implode(' AND ', array("`{$alias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$alias}`.`attribute_id` = ?", $attribute->getId()), "`{$alias}`.`store_id` = 0")), null); $select->joinLeft(array($storeAlias => $attribute->getBackendTable()), implode(' AND ', array("`{$storeAlias}`.`entity_id` = `e`.`entity_id`", $db->quoteInto("`{$storeAlias}`.`attribute_id` = ?", $attribute->getId()), $db->quoteInto("`{$storeAlias}`.`store_id` = ?", Mage::app()->getStore()->getId()))), null); } return $this; }
/** * Prepare additional price expression sql part * * @param Varien_Db_Select $select * @return Mage_Catalog_Model_Resource_Product_Collection */ protected function _preparePriceExpressionParameters($select) { // prepare response object for event $response = new Varien_Object(); $response->setAdditionalCalculations(array()); $tableAliases = array_keys($select->getPart(Zend_Db_Select::FROM)); if (in_array(self::INDEX_TABLE_ALIAS, $tableAliases)) { $table = self::INDEX_TABLE_ALIAS; } else { $table = reset($tableAliases); } // prepare event arguments $eventArgs = array('select' => $select, 'table' => $table, 'store_id' => $this->getStoreId(), 'response_object' => $response); Mage::dispatchEvent('catalog_prepare_price_select', $eventArgs); $additional = join('', $response->getAdditionalCalculations()); $this->_priceExpression = $table . '.min_price'; $this->_additionalPriceExpression = $additional; $this->_catalogPreparePriceSelect = clone $select; return $this; }
/** * Get update table query using select object for join and update * * @param Varien_Db_Select $select * @param string|array $table * @throws Varien_Db_Exception * @return string */ public function updateFromSelect(Varien_Db_Select $select, $table) { if (!is_array($table)) { $table = array($table => $table); } // get table name and alias $keys = array_keys($table); $tableAlias = $keys[0]; $tableName = $table[$keys[0]]; $query = sprintf('UPDATE %s', $this->quoteTableAs($tableName, $tableAlias)); // render JOIN conditions (FROM Part) $joinConds = array(); foreach ($select->getPart(Zend_Db_Select::FROM) as $correlationName => $joinProp) { if ($joinProp['joinType'] == Zend_Db_Select::FROM) { $joinType = strtoupper(Zend_Db_Select::INNER_JOIN); } else { $joinType = strtoupper($joinProp['joinType']); } $joinTable = ''; if ($joinProp['schema'] !== null) { $joinTable = sprintf('%s.', $this->quoteIdentifier($joinProp['schema'])); } $joinTable .= $this->quoteTableAs($joinProp['tableName'], $correlationName); $join = sprintf(' %s %s', $joinType, $joinTable); if (!empty($joinProp['joinCondition'])) { $join = sprintf('%s ON %s', $join, $joinProp['joinCondition']); } $joinConds[] = $join; } if ($joinConds) { $query = sprintf("%s\n%s", $query, implode("\n", $joinConds)); } // render UPDATE SET $columns = array(); foreach ($select->getPart(Zend_Db_Select::COLUMNS) as $columnEntry) { list($correlationName, $column, $alias) = $columnEntry; if (empty($alias)) { $alias = $column; } if (!$column instanceof Zend_Db_Expr && !empty($correlationName)) { $column = $this->quoteIdentifier(array($correlationName, $column)); } $columns[] = sprintf('%s = %s', $this->quoteIdentifier(array($tableAlias, $alias)), $column); } if (!$columns) { throw new Varien_Db_Exception('The columns for UPDATE statement are not defined'); } $query = sprintf("%s\nSET %s", $query, implode(', ', $columns)); // render WHERE $wherePart = $select->getPart(Zend_Db_Select::WHERE); if ($wherePart) { $query = sprintf("%s\nWHERE %s", $query, implode(' ', $wherePart)); } return $query; }
/** * 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; }
/** * Prepare select column list * * @param Varien_Db_Select $select * @param $groupByCondition OPTIONAL * @return array * @throws Zend_Db_Exception */ public function prepareColumnsList(Varien_Db_Select $select, $groupByCondition = null) { if (!count($select->getPart(Zend_Db_Select::FROM))) { return $select->getPart(Zend_Db_Select::COLUMNS); } $columns = $select->getPart(Zend_Db_Select::COLUMNS); $tables = $select->getPart(Zend_Db_Select::FROM); $preparedColumns = array(); foreach ($columns as $columnEntry) { list($correlationName, $column, $alias) = $columnEntry; if ($column instanceof Zend_Db_Expr) { if ($alias !== null) { if (preg_match('/(^|[^a-zA-Z_])^(SELECT)?(SUM|MIN|MAX|AVG|COUNT)\\s*\\(/i', $column, $matches)) { $column = $this->prepareColumn($column, $groupByCondition); } $preparedColumns[strtoupper($alias)] = array(null, $column, $alias); } else { throw new Zend_Db_Exception("Can't prepare expression without alias"); } } else { if ($column == Zend_Db_Select::SQL_WILDCARD) { if ($tables[$correlationName]['tableName'] instanceof Zend_Db_Expr) { throw new Zend_Db_Exception("Can't prepare expression when tableName is instance of Zend_Db_Expr"); } $tableColumns = $this->_getReadAdapter()->describeTable($tables[$correlationName]['tableName']); foreach (array_keys($tableColumns) as $col) { $preparedColumns[strtoupper($col)] = array($correlationName, $col, null); } } else { $columnKey = is_null($alias) ? $column : $alias; $preparedColumns[strtoupper($columnKey)] = array($correlationName, $column, $alias); } } } // $select->reset(Zend_Db_Select::COLUMNS); // $select->setPart(Zend_Db_Select::COLUMNS, array_values($preparedColumns)); return $preparedColumns; }
/** * @param Varien_Db_Select $select */ protected function getMainAlias($select) { $from = $select->getPart(Varien_Db_Select::FROM); $aliases = array_keys($from); return $aliases[0]; }
/** * Prepare select for load * * @return string */ protected function _prepareSelect(Varien_Db_Select $select) { $helper = Mage::getResourceHelper('core'); $unionParts = $select->getPart(Zend_Db_Select::UNION); if (!empty($unionParts)) { $select = $helper->limitUnion($select); } if ($this->_useAnalyticFunction) { return $helper->getQueryUsingAnalyticFunction($select); } return (string) $select; }
/** * @param Varien_Db_Select $select * @param $conditionString * @return mixed|null */ protected function _replaceProductTableAlias($select, $conditionString, $oldMainFromClause) { if (is_null($conditionString)) { return null; } $adapter = $this->_getReadAdapter(); if (strpos($conditionString, 'e.') === false) { return $conditionString; } $fromPart = $select->getPart(Zend_Db_Select::FROM); if (!isset($fromPart['main_table'])) { $select->join(array('main_table' => $oldMainFromClause['tableName']), "`e`.`entity_id` = `main_table`.`entity_id`", null); } $oldAlias = array('e' . '.', $adapter->quoteIdentifier('e') . '.'); $newAlias = array('main_table' . '.', $adapter->quoteIdentifier('main_table') . '.'); return $this->_replaceAliasExpr($oldAlias, $newAlias, $conditionString); }
/** * Join Tracker table for the given tracker id * * return table alias * * @param Varien_Db_Select $select * @param integer $trackerId * @param string $table * @return string */ protected function _joinTracker(Varien_Db_Select $select, $trackerId, $table) { $alias = 'tracker_' . $trackerId; // already joined if (array_key_exists($alias, $select->getPart(Varien_Db_Select::FROM))) { return $alias; } $description = $this->getReadAdapter()->describeTable($this->getTable($table)); $joins = array('campaign_id', 'variation_id', 'dimension_id', 'value_id', 'date'); foreach ($joins as $join) { if (isset($description[$join])) { $cond[] = "`report`.`{$join}` = `{$alias}`.`{$join}`"; } } $cond[] = "`{$alias}`.`tracker_id` = {$trackerId}"; // seperate join condition is a bit faster //$cond[] = "((`report`.`variation_id` = `$alias`.`variation_id`) OR (`report`.`variation_id` IS NULL && `$alias`.`variation_id` IS NULL))"; /* if($this->getParam(self::VARIATION)) { $cond[] = "`report`.`variation_id` = `$alias`.`variation_id`"; } else { $cond[] = "`$alias`.`variation_id` IS NULL"; }*/ $cond = implode(' AND ', $cond); $select->joinLeft(array($alias => $this->getTable($table)), $cond, null); return $alias; }