Helper.php

Go to the documentation of this file.

<?php
namespace Magento\Framework\DB;

class Helper extends \Magento\Framework\DB\Helper\AbstractHelper
{
    protected function _prepareOrder(\Magento\Framework\DB\Select $select, $autoReset = false)
    {
        $selectOrders = $select->getPart(\Magento\Framework\DB\Select::ORDER);
        if (!$selectOrders) {
            return [];
        }

        $orders = [];
        foreach ($selectOrders as $term) {
            if (is_array($term)) {
                if (!is_numeric($term[0])) {
                    $orders[] = sprintf('%s %s', $this->getConnection()->quoteIdentifier($term[0], true), $term[1]);
                }
            } else {
                if (!is_numeric($term)) {
                    $orders[] = $this->getConnection()->quoteIdentifier($term, true);
                }
            }
        }

        if ($autoReset) {
            $select->reset(\Magento\Framework\DB\Select::ORDER);
        }

        return $orders;
    }

    protected function _truncateAliasName($field, $reverse = false)
    {
        $string = $field;
        if (!is_numeric($field) && (strpos($field, '.') !== false)) {
            $size  = strpos($field, '.');
            if ($reverse) {
                $string = substr($field, 0, $size);
            } else {
                $string = substr($field, $size + 1);
            }
        }

        return $string;
    }

    protected function _prepareGroup(\Magento\Framework\DB\Select $select, $autoReset = false)
    {
        $selectGroups = $select->getPart(\Magento\Framework\DB\Select::GROUP);
        if (!$selectGroups) {
            return [];
        }

        $groups = [];
        foreach ($selectGroups as $term) {
            $groups[] = $this->getConnection()->quoteIdentifier($term, true);
        }

        if ($autoReset) {
            $select->reset(\Magento\Framework\DB\Select::GROUP);
        }

        return $groups;
    }

    protected function _prepareHaving(\Magento\Framework\DB\Select $select, $autoReset = false)
    {
        $selectHavings = $select->getPart(\Magento\Framework\DB\Select::HAVING);
        if (!$selectHavings) {
            return [];
        }

        $havings = [];
        $columns = $select->getPart(\Magento\Framework\DB\Select::COLUMNS);
        foreach ($columns as $columnEntry) {
            $correlationName = (string)$columnEntry[1];
            $column          = $columnEntry[2];
            foreach ($selectHavings as $having) {
                if (strpos($having, $correlationName) !== false) {
                    if (is_string($column)) {
                        $havings[] = str_replace($correlationName, $column, $having);
                    } else {
                        throw new \Zend_Db_Exception(
                            sprintf("Can't prepare expression without column alias: '%s'", $correlationName)
                        );
                    }
                }
            }
        }

        if ($autoReset) {
            $select->reset(\Magento\Framework\DB\Select::HAVING);
        }

        return $havings;
    }

    protected function _assembleLimit($query, $limitCount, $limitOffset, $columnList = [])
    {
        if ($limitCount !== null) {
            $limitCount = intval($limitCount);
            if ($limitCount <= 0) {
                //throw new \Exception("LIMIT argument count={$limitCount} is not valid");
            }

            $limitOffset = intval($limitOffset);
            if ($limitOffset < 0) {
                //throw new \Exception("LIMIT argument offset={$limitOffset} is not valid");
            }

            if ($limitOffset + $limitCount != $limitOffset + 1) {
                $columns = [];
                foreach ($columnList as $columnEntry) {
                    $columns[] = $columnEntry[2] ? $columnEntry[2] : $columnEntry[1];
                }
                $query = sprintf('%s LIMIT %s, %s', $query, $limitCount, $limitOffset);
            }
        }

        return $query;
    }

    public function prepareColumnsList(\Magento\Framework\DB\Select $select, $groupByCondition = null)
    {
        if (!count($select->getPart(\Magento\Framework\DB\Select::FROM))) {
            return $select->getPart(\Magento\Framework\DB\Select::COLUMNS);
        }

        $columns          = $select->getPart(\Magento\Framework\DB\Select::COLUMNS);
        $tables           = $select->getPart(\Magento\Framework\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 == \Magento\Framework\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->getConnection()->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;
    }

    public function addGroupConcatColumn(
        $select,
        $fieldAlias,
        $fields,
        $groupConcatDelimiter = ',',
        $fieldsDelimiter = '',
        $additionalWhere = ''
    ) {
        if (is_array($fields)) {
            $fieldExpr = $this->getConnection()->getConcatSql($fields, $fieldsDelimiter);
        } else {
            $fieldExpr = $fields;
        }
        if ($additionalWhere) {
            $fieldExpr = $this->getConnection()->getCheckSql($additionalWhere, $fieldExpr, "''");
        }
        $separator = '';
        if ($groupConcatDelimiter) {
            $separator = sprintf(" SEPARATOR '%s'", $groupConcatDelimiter);
        }
        $select->columns([$fieldAlias => new \Zend_Db_Expr(sprintf('GROUP_CONCAT(%s%s)', $fieldExpr, $separator))]);
        return $select;
    }

    public function getDateDiff($startDate, $endDate)
    {
        $dateDiff = '(TO_DAYS(' . $endDate . ') - TO_DAYS(' . $startDate . '))';
        return new \Zend_Db_Expr($dateDiff);
    }

    public function addLikeEscape($value, $options = [])
    {
        $value = $this->escapeLikeValue($value, $options);
        return new \Zend_Db_Expr($this->getConnection()->quote($value));
    }
}