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