/** * Render WHERE section * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::FROM) && $select->getPart(Select::WHERE)) { $sql .= ' ' . Select::SQL_WHERE . ' ' . implode(' ', $select->getPart(Select::WHERE)); } return $sql; }
/** * Render GROUP BY section * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::FROM) && $select->getPart(Select::GROUP)) { $group = []; foreach ($select->getPart(Select::GROUP) as $term) { $group[] = $this->quote->quoteIdentifier($term); } $sql .= ' ' . Select::SQL_GROUP_BY . ' ' . implode(",\n\t", $group); } return $sql; }
/** * Check structure of sql query * * @param \Magento\Framework\DB\Select $select * @return array */ public function fetchAllCallback(\Magento\Framework\DB\Select $select) { $whereParts = $select->getPart(\Magento\Framework\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(\Magento\Framework\DB\Select::ORDER); $this->assertCount(1, $orderParts); $expectedOrderParts = ['rule_name', 'ASC']; $this->assertEquals($expectedOrderParts, $orderParts[0]); return $this->_rules; }
/** * Render FROM & JOIN's section * * @param Select $select * @param string $sql * @return string * @throws \Zend_Db_Select_Exception */ public function render(Select $select, $sql = '') { /* * If no table specified, use RDBMS-dependent solution * for table-less query. e.g. DUAL in Oracle. */ $source = $select->getPart(Select::FROM); if (empty($source)) { $source = []; } $from = []; foreach ($source as $correlationName => $table) { $tmp = ''; $joinType = $table['joinType'] == Select::FROM ? Select::INNER_JOIN : $table['joinType']; // Add join clause (if applicable) if (!empty($from)) { $tmp .= ' ' . strtoupper($joinType) . ' '; } $tmp .= $this->getQuotedSchema($table['schema']); $tmp .= $this->getQuotedTable($table['tableName'], $correlationName); // Add join conditions (if applicable) if (!empty($from) && !empty($table['joinCondition'])) { $tmp .= ' ' . Select::SQL_ON . ' ' . $table['joinCondition']; } // Add the table name and condition add to the list $from[] = $tmp; } // Add the list of all joins if (!empty($from)) { $sql .= ' ' . Select::SQL_FROM . ' ' . implode("\n", $from); } return $sql; }
/** * Render DISTINCT section * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::DISTINCT)) { $sql .= ' ' . Select::SQL_DISTINCT . ' '; } return $sql; }
/** * Render FOR UPDATE section * * @param Select $select * @param string $sql * @return string * @throws \Zend_Db_Select_Exception */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::FOR_UPDATE)) { $sql .= ' ' . Select::SQL_FOR_UPDATE; } return $sql; }
/** * Render UNION section * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::UNION)) { $sql = ''; $parts = count($select->getPart(Select::UNION)); foreach ($select->getPart(Select::UNION) as $cnt => $union) { list($target, $type) = $union; if ($target instanceof Select) { $target = $target->assemble(); } $sql .= $target; if ($cnt < $parts - 1) { $sql .= ' ' . $type . ' '; } } } return $sql; }
/** * Render SELECT statement * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { $sql = Select::SQL_SELECT; foreach ($this->renderers as $renderer) { if (in_array($renderer['part'], [Select::COLUMNS, Select::FROM]) || $select->getPart($renderer['part'])) { $sql = $renderer['renderer']->render($select, $sql); } } return $sql; }
/** * Render LIMIT section * * @param Select $select * @param string $sql * @return LimitExpression|string */ public function render(Select $select, $sql = '') { $count = 0; $offset = 0; if (!empty($select->getPart(Select::LIMIT_OFFSET))) { $offset = (int) $select->getPart(Select::LIMIT_OFFSET); $count = PHP_INT_MAX; } if (!empty($select->getPart(Select::LIMIT_COUNT))) { $count = (int) $select->getPart(Select::LIMIT_COUNT); } /* * Add limits clause */ if ($count > 0) { $sql = new LimitExpression($sql, $count, $offset); } return $sql; }
/** * Render ORDER BY section * * @param Select $select * @param string $sql * @return string */ public function render(Select $select, $sql = '') { if ($select->getPart(Select::ORDER)) { $order = []; foreach ($select->getPart(Select::ORDER) as $term) { if (is_array($term)) { if (is_numeric($term[0]) && strval(intval($term[0])) == $term[0]) { $order[] = (int) trim($term[0]) . ' ' . $term[1]; } else { $order[] = $this->quote->quoteIdentifier($term[0]) . ' ' . $term[1]; } } elseif (is_numeric($term) && strval(intval($term)) == $term) { $order[] = (int) trim($term); } else { $order[] = $this->quote->quoteIdentifier($term); } } $sql .= ' ' . Select::SQL_ORDER_BY . ' ' . implode(', ', $order) . PHP_EOL; } return $sql; }
/** * Render COLUMNS section * * @param Select $select * @param string $sql * @return null|string * @throws \Zend_Db_Select_Exception */ public function render(Select $select, $sql = '') { if (!count($select->getPart(Select::COLUMNS))) { return null; } $columns = []; foreach ($select->getPart(Select::COLUMNS) as $columnEntry) { list($correlationName, $column, $alias) = $columnEntry; if ($column instanceof \Zend_Db_Expr) { $columns[] = $this->quote->quoteColumnAs($column, $alias); } else { if ($column == Select::SQL_WILDCARD) { $column = new \Zend_Db_Expr(Select::SQL_WILDCARD); $alias = null; } if (empty($correlationName)) { $columns[] = $this->quote->quoteColumnAs($column, $alias); } else { $columns[] = $this->quote->quoteColumnAs([$correlationName, $column], $alias); } } } return $sql . ' ' . implode(', ', $columns); }
/** * Prepare additional price expression sql part * * @param \Magento\Framework\DB\Select $select * @return $this */ protected function _preparePriceExpressionParameters($select) { // prepare response object for event $response = new \Magento\Framework\DataObject(); $response->setAdditionalCalculations([]); $tableAliases = array_keys($select->getPart(\Magento\Framework\DB\Select::FROM)); if (in_array(self::INDEX_TABLE_ALIAS, $tableAliases)) { $table = self::INDEX_TABLE_ALIAS; } else { $table = reset($tableAliases); } // prepare event arguments $eventArgs = ['select' => $select, 'table' => $table, 'store_id' => $this->getStoreId(), 'response_object' => $response]; $this->_eventManager->dispatch('catalog_prepare_price_select', $eventArgs); $additional = join('', $response->getAdditionalCalculations()); $this->_priceExpression = $table . '.min_price'; $this->_additionalPriceExpression = $additional; $this->_catalogPreparePriceSelect = clone $select; return $this; }
/** * Get value field * * @return string */ private function getValueFiled() { $field = $this->select->getPart(Select::COLUMNS)[0]; return $field[1]; }
/** * Get update table query using select object for join and update * * @param Select $select * @param string|array $table * @return string * @throws \Magento\Framework\DB\DBException */ public function updateFromSelect(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 \Magento\Framework\DB\DBException('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; }
/** * Prepare select column list * * @param \Magento\Framework\DB\Select $select * @param string|null $groupByCondition OPTIONAL * @return mixed|array * @throws \Zend_Db_Exception * @SuppressWarnings(PHPMD.CyclomaticComplexity) * @SuppressWarnings(PHPMD.UnusedFormalParameter) */ public function prepareColumnsList(\Magento\Framework\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 = []; 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)) { $column = new \Zend_Db_Expr($column); } $preparedColumns[strtoupper($alias)] = [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)] = [$correlationName, $col, null]; } } else { $columnKey = $alias === null ? $column : $alias; $preparedColumns[strtoupper($columnKey)] = [$correlationName, $column, $alias]; } } } return $preparedColumns; }